Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 843
ina Avatar asked Feb 21 '12 15:02

ina


People also ask

Can I use UUID as primary key MySQL?

By using UUID, you can generate the primary key value of the parent table up front and insert rows into both parent and child tables at the same time within a transaction.

Can MySQL generate UUID?

The MySQL UUID() function is used to return a Universal Unique Identifier (UUID) generated according to RFC 4122, “A Universally Unique IDentifier (UUID) URN Namespace”. A key point to note about UUIDs is that they are designed such that they are globally unique in space and time.

Should you use UUID as primary key?

UUIDs as primary key aren't a slam drunk, but do have some advantages: The fact that they're random means that they don't rely on a single sequence to be generated. Multiple entities can generate IDs independently, but still store them to a shared data store without clobbering each other.

Is UUID incremental?

UUID always occupies 16 bytes. For Auto Increment Integer, when stored as Long format, it occupies 8 bytes. If the table itself has only a few columns, the extra primary key space overhead will become more significant.


1 Answers

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.

like image 79
Colin O'Dell Avatar answered Oct 05 '22 13:10

Colin O'Dell