10

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

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

how can i retrieve the generated uuid immediately after inserting the record ?

Alaa Jabre
  • 1,843
  • 5
  • 26
  • 52

4 Answers4

26
  1. char(36) is better
  2. you cannot. The only solution is to perform 2 separated queries:

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

where $uuid is the value retrieved on the 1st step.

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • 5
    char(36) is not really much better, binary(16) would be most efficient – pospi Dec 14 '11 at 00:24
  • @pospi: char(36) is better than varchar. And with binary you'll experience issues on string comparisons (binary doesn't respect collations) – zerkms Dec 14 '11 at 02:14
  • 1
    that's correct, but if you're inserting binary data then you wouldn't be treating it like a string or expecting comparisons to work like that anyway - you'd be reading out with HEX() and querying using hex notation or UNHEX()... and any comparisons you did between the field would be done on the raw binary data instead of its characters (: – pospi Dec 16 '11 at 01:18
  • I've expanded on my original answer [here](http://stackoverflow.com/questions/4933296/get-the-generated-uuid-after-insert-php/8498220#8498220) (: – pospi Sep 08 '12 at 03:57
  • `CHAR(36)` becomes 3*36 bytes long with UTF-8, plus the length byte(s). So your best options would be (A) something binary, (B) `ASCII CHAR`, or (C) `VARCHAR`. – Timo Sep 30 '16 at 14:28
  • @Timo no it does not. `UUID()` characters are all single bytes. Please check how exactly utf-8 encodes unicode codepoints if you decide to continue discussing it. – zerkms Oct 01 '16 at 06:46
  • UUID() is the data, not the column definition. Column width depends on your table/column charset. The size of a `CHAR(36)` depends on the data that *could* be in it (defined by charset), *not* on the data that you just *happen to be storing in it at this time*. Yes, for UUID() it would not *need* to be as large as it is. But (if UTF-8) you are perfectly allowed to store 36 Japanese characters in it at any time, which take up a lot more space, and the column has already reserved the space to make this possible, whether you will use it or not. A reason to be cautious with UTF-8 `CHAR`. – Timo Oct 02 '16 at 09:58
11

You can do everything you need to with SQL triggers. The following SQL adds a trigger on tablename.table_id to automatically create the primary key UUID when inserting, then stores the newly created ID into an SQL variable for retrieval later:

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 the UUID in binary form to a binary(16) field to save storage space and greatly increase query speed.

edit: the trigger should check for an existing column value before inserting its own UUID in order to mimic the ability to provide values for table primary keys in MySQL - without this, any values passed in will always be overridden by the trigger. The example has been updated to use ASCII() = 0 to check for the existence of the primary key value in the INSERT, which will detect empty string values for a binary field.

edit 2: after a comment here it has since been pointed out to me that using BEFORE INSERT has the effect of setting the @last_uuid variable even if the row insert fails. I have updated my answer to use AFTER INSERT - whilst I feel this is a totally fine approach under general circumstances it may have issues with row replication under clustered or replicated databases. If anyone knows, I would love to as well!

To read the new row's insert ID back out, just run SELECT @last_uuid.

When querying and reading such binary values, the MySQL functions HEX() and UNHEX() will be very helpful, as will writing your query values in hex notation (preceded by 0x). The php-side code for your original answer, given this type of trigger applied to table1, 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);

Following up in response to @ina's comment:

A UUID is not a string, even if MySQL chooses to represent it as such. It's binary data in its raw form, and those dashes are just MySQL's friendly way of representing it to you.

The most efficient storage for a UUID is to create it as UNHEX(REPLACE(UUID(),'-','')) - this will remove that formatting and convert it back to binary data. Those functions will make the original insertion slower, but all following comparisons you do on that key or column will be much faster on a 16-byte binary field than a 36-character string.

For one, character data requires parsing and localisation. Any strings coming in to the query engine are generally being collated automatically against the character set of the database, and some APIs (wordpress comes to mind) even run CONVERT() on all string data before querying. Binary data doesn't have this overhead. For the other, your char(36) is actually allocating 36 characters, which means (if your database is UTF-8) each character could be as long as 3 or 4 bytes depending on the version of MySQL you are using. So a char(36) can range anywhere from 36 bytes (if it consists entirely of low-ASCII characters) to 144 if consisting entirely of high-order UTF8 characters. This is much larger than the 16 bytes we have allocated for our binary field.

Any logic performed on this data can be done with UNHEX(), but is better accomplished by simply escaping data in queries as hex, prefixed with 0x. This is just as fast as reading a string, gets converted to binary on the fly and directly assigned to the query or cell in question. Very fast. Reading data out is slightly slower - you have to call HEX() on all binary data read out of a query to get it in a useful format if your client API doesn't deal well with binary data (PHP in paricular will usually determine that binary strings === null and will break them if manipulated without first calling bin2hex(), base64_encode() or similar) - but this overhead is about as minimal as character collation and more importantly is only being called on the actual cells SELECTed, not all cells involved in the internal computations of a query result.

So of course, all these small speed increases are very minimal and other areas result in small decreases - but when you add them all up binary still comes out on top, and when you consider use cases and the general 'reads > writes' principle it really shines.

... and that's why binary(16) is better than char(36).

Community
  • 1
  • 1
pospi
  • 3,540
  • 3
  • 27
  • 26
  • It's not only mysql's decision to display UUID as such, it is its representation by recommendation. – zerkms Sep 08 '12 at 06:05
  • 1
    Even so, it's still just for representation. All those dashes do is denote which parts of the UUID are generated from the timestamp, which preserve uniqueness and which are based on the MAC address of the host machine. There's no reason you'd need to know any of this information from a UUID, and no reason you couldn't figure it out regardless if you knew the byte offsets and [variant of UUID](http://en.wikipedia.org/wiki/Uuid#Variants_and_versions) being generated. If anything all removing them does is further obfuscate any of this information from being derived from that ID. – pospi Sep 08 '12 at 14:41
  • "is actually allocating 36 characters, which means (if your database is UTF-8) either 3 or 4 bytes per character" --- that's just wrong. UTF-8 string for ascii-safe characters will take exactly 36 bytes – zerkms Sep 08 '12 at 21:16
  • I have updated my answer in reference to some comments on the `BEFORE INSERT` trigger, please see *edit2* note (: – pospi Sep 14 '12 at 01:24
6

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;
roneo
  • 1,179
  • 13
  • 22
  • What happens if the insert fails? Do you still get the id returned? Does it mask the insert failure? I know this answer is old. Just posting this in case someone still stumbles on it (I did). – Cully May 20 '19 at 18:45
  • @Cully, it depends on the library or the language that you are using. If the insert fails, some languages will throw an exception and in your code, you need to catch the exception, some frameworks will just throw the error string as output.. So a lot depends on the framework you use to access the DB – roneo May 21 '19 at 06:28
-1

Depending on how the uuid() function is implemented, this is 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. Ivan's suggestion looks it might solve the immediate problem - however I thought this only returned the value generated for an auto-increment field - indeed that's what the manual says.

Also what's the benefit of using a uuid()? Its computationally expensive to generate, requires a lot of storage, increases the cost of querying the data and is not cryptographically secure. Use a sequence generator or autoincrement instead.

Regardless if you use a sequence generator or uuid, if you must use this as the only unique key on the database, then you'll need to assign the value first, read it back into phpland and embed/bind the value as a literal to the subsequent insert query.

Community
  • 1
  • 1
symcbean
  • 47,736
  • 6
  • 59
  • 94
  • Well, it is not a bad practice. It is just another way to solve an issue: http://www.codinghorror.com/blog/2007/03/primary-keys-ids-versus-guids.html – zerkms Feb 08 '11 at 13:14
  • And how the hell PK is related to crypto?! – zerkms Feb 08 '11 at 13:14
  • And of course binary logging will not cause any problems with uuid, either in cluster or replication. – zerkms Feb 08 '11 at 13:15
  • 1
    I have tested the trigger I've written above with MySQL Cluster and also Gallera - no issues with it. The fact that it handles the presence of the UUID key on the `INSERT` trigger means that the node responsible for generating the row creates the key upon running its transaction, and that key is then present in the data provided to other nodes when the data cascades to replicated tables. – pospi Sep 08 '12 at 04:00