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

Question

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?

score 15 · Answer 1 · edited Mar 08 '16 at 18:43

15

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.

edited Mar 08 '16 at 18:43

Darryl Hein

142,451
95
218
261

answered Feb 21 '12 at 16:12

Colin O'Dell

8,386
8
38
75

Hmm, using this method, I'd have to check if it's a unique uuid in MySQL, and if it's unique across servers. It seems an extra check-for-unique or data pull is required each way? – ina Feb 24 '12 at 03:19
4

If you use randomly-generated UUIDs, you will not have this problem. Statistically, if you generated **1 billion** UUIDs **every second for 100 years**, there's only a 50% chance that **just one** of them will collide. (Source: http://en.wikipedia.org/wiki/Universally_unique_identifier#Random_UUID_probability_of_duplicates) Although you will likely never see a collision, it would still be smart to make the UUID a primary key in MySQL and sync the servers regularly. – Colin O'Dell Feb 24 '12 at 14:32

score 0 · Answer 2 · answered Mar 02 '23 at 12:38

0

Depending on your exact szenario, I would probbably use a stored procedure. That way, you can

generate a mySQL UUID(),
store it in a variable,
execute the INSERT statement
SELECT the variable

answered Mar 02 '23 at 12:38

LukasKroess

125
2
13

score -1 · Answer 3 · edited Jul 31 '18 at 13:29

-1

I tried the first step: $uuid = UUID::v4(); but it hung my server, so another idea came to mind which I tried as follows:

fetch data from query $sql1 = SELECT UUID() AS uuid ;
then store in variable $uuid

and then use below

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

edited Jul 31 '18 at 13:29

JustBaron

2,319
7
25
37

answered Apr 27 '14 at 00:00

Amadeus

1

Using SQL to generate the UUID, rather than PHP, offers no benefits and will be slower as it will require multiple queries to the DB server (even if that is on the same machine). – HappyDog Apr 09 '22 at 20:15

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

3 Answers3

Linked