MySQL UUID primary key - generated by PHP or by MySQL?

php mysql random uuid lastinsertid
ina picture ina · Feb 21, 2012 · Viewed 11.7k times · Source

I was under the impression that just having MySQL generate the primary key via UUID() would make the key unique across servers, etc.

But, there is no way to fetch the last inserted UUID, which requires that an extra select statement be done each time I insert.

Is it possible to have PHP generate the exact same UUID() that MySQL would generate?

Answer

Colin O'Dell picture Colin O'Dell · Feb 21, 2012

No, it's not possible to have PHP generate the exact same UUID() as MySQL because it's a (completely) random number.

It sounds like your problem is that you like using UUID() in MySQL but don't want to execute an extra query to figure out what the new UUID is.

So why not have PHP create the UUID to be used as the primary key in your INSERT query?? This comment on php.net should show you how to do this.

Using those sample functions:

$uuid = UUID::v4();
$sql = "INSERT INTO mytable (uuid, foo) VALUES ('{$uuid}', 'bar');";
echo "The UUID is: ". $uuid;

Edit: There are several methods on that page which generate different types of UUIDs. v4 is pseudo-random, but you could use a different version or create your own UUID generator.

Related questions

MySQL: Select Random Entry, but Weight Towards Certain Entries

I've got a MySQL table with a bunch of entries in it, and a column called "Multiplier." The default (and most common) value for this column is 0, but it could be any number. What I need to do is select …

Read More
Generating unique codes in PHP/MySQL?

I'm working with a client that needs to generate millions of the alphanumeric codes used in magazine scratch-off cards, bottlecap prizes, and so on. They have to be short enough to print on a cap, they want to make sure …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More