How to create a public id?

I have a database. As you can see the primary key is an auto_increment and is also unique. I read that publically sharing a row’s primary key of a table to the public is unsafe. I want to assign each row in customers a unique ID that I can publically share. How can I do this without having to specify each time what the public_id is in the INSERT statement? The database should automatically find a unique ID to assign to that row just like it does for id because of auto_increment.

CREATE TABLE customers (
id int primary key auto_increment,
name varchar(32) not null,
-- public_id (an ID I can give to the public to uniquely identify this row
);

INSERT INTO customers (name) VALUES ('Bob'), ('Sarah'), ('Bob');

Answer

Well, here’s one way:

CREATE TABLE customers (
    id int primary key auto_increment,
    name varchar(32) not null,
    public_id char(36) not null unique default uuid()
);

Note that the manual says:

Warning

Although UUID() values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way.

So this is simple, and maybe will float your goat, but we can also try better:

CREATE TABLE customers (
    id int primary key auto_increment,
    name varchar(32) not null,
    public_id char(24) not null unique default to_base64(random_bytes(18))
);

This will be a nice and dense identifier, but it will have characters + and / which don’t play well with URLs. You can encode them, of course, but if you want to go one lazier, you can also do this:

CREATE TABLE customers (
    id int primary key auto_increment,
    name varchar(32) not null,
    public_id char(32) not null unique default hex(random_bytes(16))
);

Mind you, the identifier will get quite a bit longer this way.

To get the best of both worlds, we can do this, at the expense of a really long default value:

CREATE TABLE customers (
    id int primary key auto_increment,
    name varchar(32) not null,
    public_id char(24) not null unique default replace(replace(to_base64(random_bytes(18)), '+', '_'), '/', '-')
);

Also note that messing around with MD5()/SHA()/SHA1()/SHA2() is no better than just generating a random hex string with a given length.