Get generated uuid after php insert

Question

I have a varchar table field type (36) and I want to generate it dynamically with mysql, so I used this code:

$sql_code = 'insert into table1 (id, text) values (uuid(),'some text');'; mysql_query($sql_code); 

how can i get the generated uuid right after inserting a record?

Answer 1

• char(36) better

• You can not. The only solution is to execute 2 separate requests:

  • SELECT UUID()
  • INSERT INTO table1 (id, text) VALUES ($uuid, 'text')

where $ uuid is the value obtained in the first step.

Answer 2

You can do whatever you need with SQL triggers. The following SQL adds a trigger to tablename.table_id to automatically create the UUID of the primary key upon insertion, and then saves the newly created ID to the SQL variable for later retrieval:

 CREATE TRIGGER `tablename_newid` AFTER INSERT ON `tablename` FOR EACH ROW BEGIN IF ASCII(NEW.table_id) = 0 THEN SET NEW.table_id = UNHEX(REPLACE(UUID(),'-','')); END IF; SET @last_uuid = NEW.table_id; END 

As a bonus, it inserts binary UUIDs into a binary (16) field to save storage space and significantly increase the request speed.

edit: the trigger must check the existing column value before inserting its own UUID in order to simulate the possibility of providing values ​​for the primary keys of the table in MySQL - without this, any transferred values ​​will always be overridden by the trigger. The example has been updated to use ASCII() = 0 to check for a primary key value in INSERT that will detect empty string values ​​for a binary field.

edit 2: after a comment here, he was told that using BEFORE INSERT affects the setting of @last_uuid variable, even if the line fails. I updated my answer on using AFTER INSERT - while I feel this is a great approach under general circumstances, it may have problems with row replication in clustered or replicated databases. If anyone knows, I would like too!

To read the new row insert identifier, simply run SELECT @last_uuid .

When querying and reading such binary values, the MySQL HEX() and UNHEX() functions will be very useful, as they will record your query values ​​in hexadecimal notation (preceding 0x ). The php side code for your original answer, given this type of trigger applied to table 1, would be:

 // insert row $sql = "INSERT INTO table1(text) VALUES ('some text')"; mysql_query($sql); // get last inserted UUID $sql = "SELECT HEX(@last_uuid)"; $result = mysql_query($sql); $row = mysql_fetch_row($result); $id = $row[0]; // perform a query using said ID mysql_query("SELECT FROM table1 WHERE id = 0x" . $id); 

In response to the @ comment :

UUID is not a string, even if MySQL decides to present it as such. This is raw binary data, and this dash is just a MySQL friendly way to present it to you.

The most efficient repository for a UUID is to create it as UNHEX(REPLACE(UUID(),'-','')) - this will remove this formatting and convert it back to binary data. These functions will slow down the initial insertion, but all subsequent comparisons that you make on this key or column will be much faster in a 16-byte binary field than in a 36-character string.

For one, character data requires parsing and localization. Any strings that enter the query engine are usually mapped automatically to the database character set, and some APIs (Wordpress comes to mind) even run CONVERT() on all string data before the query. Binary data does not have this overhead. For another, your char(36) actually allocates 36 characters, which means (if your database is UTF-8), each character can be as long as 3 or 4 bytes depending on the version of MySQL you are using. Thus, char(36) can range from 36 bytes (if it consists entirely of low ASCII characters) to 144 if it consists solely of high order UTF8 characters. This is much more than the 16 bytes we allocated for our binary field.

Any logic performed on this data can be executed using UNHEX() , but it is better done by simply escaping the data in the queries as hexadecimal, with the prefix 0x . It is as fast as reading a line, converted to binary code on the fly, and directly assigned to the corresponding query or cell. Very fast. Reading data is a bit slower - you have to call HEX() for all binary data read from the request in order to get it in a useful format if your client API cannot handle binary data (PHP privately determines that binary strings === null and will be split into them if they are processed without the first call to bin2hex() , base64_encode() or the like) - but this overhead is about as minimal as sorting characters and, more importantly, is called only for actual SELECT cells ed, not all cells involved in internal calculations of the query result.

So, all these small speed increases are very minimal, and other areas lead to small reductions - but when you add them, everything binary still comes out from the top, and when you consider the use cases and general “readings”> it says that it really shines.

... and why binary(16) better than char(36) .

Answer 3

Its pretty easy actually you can pass this to mysql and it will return the inserted id.

 set @id=UUID(); insert into <table>(<col1>,<col2>) values (@id,'another value'); select @id; 

Answer 4

Depending on how the uuid () function is implemented, this is a very bad programming practice - if you try to do this with binary logging enabled (i.e. in a cluster), then the insert will most likely fail . Assuming that he could solve the immediate problem, Ivan suggested that this only returns the value generated for the auto-increment field - indeed that is what the manual says .

And what is the use of using uuid ()? Its computationally expensive generation, requires a large amount of memory, increases the cost of requesting data and is not cryptographically protected. Use a sequence generator or auto-increment instead.

Regardless of whether you use the sequence generator or uuid, if you should use it as the only unique key in the database, you need to first assign the value, read it back into phpland and insert / bind the value as a literal for the subsequent insert request.