Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You can do everything you need to with SQL triggers. The following SQL adds a trigger on <code>tablename.table_id</code> to automatically create the primary key UUID when inserting, then stores the newly created ID into an SQL variable for retrieval later:</p> <pre><code>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 </code></pre> <p>As a bonus, it inserts the UUID in binary form to a binary(16) field to save storage space and greatly increase query speed.</p> <p><em>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 <code>ASCII() = 0</code> to check for the existence of the primary key value in the INSERT, which will detect empty string values for a binary field.</em></p> <p><em>edit 2: after a comment <a href="https://stackoverflow.com/questions/4687196/mysql-insert-id-or-something-like-that-to-return-last-mysql-uuid/8498129#comment16643769_8498129">here</a> it has since been pointed out to me that using <code>BEFORE INSERT</code> has the effect of setting the <code>@last_uuid</code> variable even if the row insert fails. I have updated my answer to use <code>AFTER INSERT</code> - 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!</em></p> <p>To read the new row's insert ID back out, just run <code>SELECT @last_uuid</code>.</p> <p>When querying and reading such binary values, the MySQL functions <code>HEX()</code> and <code>UNHEX()</code> will be very helpful, as will writing your query values in hex notation (preceded by <code>0x</code>). The php-side code for your original answer, given this type of trigger applied to table1, would be:</p> <pre><code>// 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); </code></pre> <p>Following up in response to <a href="https://stackoverflow.com/questions/4933296/get-the-generated-uuid-after-insert-php/4933319#comment16467232_4933319">@ina's comment</a>:</p> <p>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.</p> <p>The most efficient storage for a UUID is to create it as <code>UNHEX(REPLACE(UUID(),'-',''))</code> - 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.</p> <p>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 <code>CONVERT()</code> on all string data before querying. Binary data doesn't have this overhead. For the other, your <code>char(36)</code> is actually allocating 36 <em>characters</em>, which means (if your database is UTF-8) each character could be as long as <a href="http://mzsanford.wordpress.com/2010/12/28/mysql-and-unicode/" rel="nofollow noreferrer">3 or 4 bytes</a> depending on the version of MySQL you are using. So a <code>char(36)</code> 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 <em>much</em> larger than the 16 bytes we have allocated for our binary field.</p> <p>Any logic performed on this data can be done with <code>UNHEX()</code>, but is better accomplished by simply escaping data in queries as hex, prefixed with <code>0x</code>. 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 <code>HEX()</code> 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 <code>=== null</code> and will break them if manipulated without first calling <code>bin2hex()</code>, <code>base64_encode()</code> or similar) - but this overhead is about as minimal as character collation and more importantly is only being called on the actual cells <code>SELECT</code>ed, not all cells involved in the internal computations of a query result.</p> <p>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 <code>binary</code> still comes out on top, and when you consider use cases and the general 'reads > writes' principle it really shines.</p> <p>... and that's why <code>binary(16)</code> is better than <code>char(36)</code>.</p>
    singulars
    1. This table or related slice is empty.
    1. POGet the generated uuid after insert php
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USpospi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt's not only mysql's decision to display UUID as such, it is its representation by recommendation.
      singulars
      1. PO
      1. USzerkms
    2. COEven 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.
      singulars
      1. PO
      1. USpospi
    3. CO"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
      singulars
      1. PO
      1. USzerkms
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload