How to generate unique id in MySQL?

How to generate unique id in MySQL?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Generating Unique IDs in MySQL: Beyond Simple Autocrement

Developing a script in PHP where you need to generate unique identifiers like gHYtUUi5b presents a specific architectural challenge. While you can rely on numerous PHP functions to create random strings, the fundamental question remains: how do you guarantee uniqueness without risking collisions? Relying solely on client-side generation or basic PHP randomness is inherently risky because it lacks a centralized source of truth regarding what IDs are currently in use. The robust solution requires shifting the logic to the database layer, specifically leveraging MySQL's capabilities to handle concurrency and collision detection automatically.

The most straightforward approach involves generating a random candidate string within your application and attempting to insert it into the database. You would generate an 11-character alphanumeric string using PHP functions like bin2hex(random_bytes()) or similar logic, then attempt to INSERT it into a column defined as VARCHAR(11) with a UNIQUE constraint. If the insertion fails due to a duplicate key error, you simply discard that candidate and try again. This retry loop is standard practice because the probability of generating a collision in a short 11-character space is negligible for most use cases, making this an efficient pattern known as "retry-on-collision."

However, if your application architecture prefers to offload the generation entirely to MySQL, you can leverage the database engine to create these IDs directly. While UUID (Universally Unique Identifier) guarantees uniqueness, its 36-character length often exceeds the aesthetic or storage requirements of an 11-character ID. Instead, you can use a combination of system functions. For instance, UUID() in MySQL generates a full UUID, but you can extract specific portions or combine hashes to shorten them. A more tailored approach involves using CONCAT with MD5(), SUBSTRING, and random numbers to craft an ID that fits your specific length constraints while maintaining uniqueness within the table's scope.

INSERT INTO users (id, name)
VALUES (
    CONCAT(SUBSTRING(MD5(UUID()), 1, 4), 
           SUBSTRING(CHAR(65+RAND()*26), 1, 1), 
           SUBSTRING(CHAR(65+RAND()*26), 1, 1),
           LEFT(CONCAT(FLOOR(RAND() * 1000000), '0'), 4)
    ),
    'New User'
);

Note that the example above demonstrates a complex formula; in practice, you should ensure the resulting string strictly adheres to your required character set (alphanumeric). A simpler method often used with UNIQUE constraints is to generate a UUID and truncate it. Since UUID() creates a unique value globally, truncating the first few characters usually retains enough entropy to prevent collisions within a single database instance, provided you are not scaling across multiple nodes without coordination.

If you are working on a Laravel project, consider how this logic integrates with your model. You can define a scope or a boot method in your model to handle this generation automatically upon creation:

class User extends Model
{
    public function generateUniqueId()
    {
        while (true) {
            $id = strtoupper(substr(uniqid('', true), 0, 11));
            
            // Ensure it matches the alphanumeric requirement and length
            if (preg_match('/^[A-Za-z0-9]{11}$/', $id)) {
                return $id;
            }
        }
    }

    public static function createWithId(string $name)
    {
        $id = new self(); // Initialize instance to use scope if needed, or call method directly
        return new static([
            'id' => self::generateUniqueId(),
            'name' => $name
        ]);
    }
}

In this implementation, uniqid() with a random component combined with regex validation ensures the format is correct before insertion. The database's unique constraint acts as the final safety net. This hybrid approach balances the ease of PHP string manipulation with the absolute certainty of database constraints.

It is important to remember that uniqueness is relative to the dataset. An ID generated on a local development machine might collide with one from a production server if they share the same namespace without coordination. For high-volume systems, relying on UNIQUE indexes combined with retry logic remains the industry standard because it scales horizontally without requiring complex distributed locking mechanisms.

Our network