Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The <code>ID</code> column of your table is declared as <code>BINARY(16)</code>, and your <code>UUID_ENCODE</code> expects a <code>CHAR(36)</code>. When you invoke your <code>UUID_ENCODE</code> function directly without a trigger, it correctly receives your 36 characters string. When you use the trigger instead, the value you are inserting is first converted to the type of the column, so <code>NEW.ID</code> will contain the results of <code>CAST('323febe6-cd89-4773-a46c-aab794fb7cbc' AS BINARY(16)</code>. When the trigger invokes the function, it will cast again the value of <code>NEW.ID</code> to the type expected by your function. So, this is the value your function will receive:</p> <pre><code>SELECT CAST(CAST('323febe6-cd89-4773-a46c-aab794fb7cbc' AS BINARY(16)) AS CHAR); 323febe6-cd89-47 </code></pre> <p>As you can see, your function receives the value truncated. The results you are getting are equivalent to:</p> <pre><code>SELECT UUID_DECODE(UUID_ENCODE('323febe6-cd89-47')); 000000000000-0000-0032-3feb-e6cd8947 </code></pre> <p><strong>UPDATE</strong></p> <p>One way to implement the desired functionality in a trigger would be to add a dummy nullable column with the type your function expects:</p> <pre><code>CREATE TABLE test (ID BINARY(16) KEY DEFAULT 0, CHARID CHAR(36) NULL); CREATE TRIGGER test_uuid_encode BEFORE INSERT ON test FOR EACH ROW BEGIN SET NEW.ID = UUID_ENCODE(NEW.CHARID) , NEW.CHARID = NULL; END </code></pre> <p>This way you can do:</p> <pre><code>INSERT test (CHARID) VALUES ('323febe6-cd89-4773-a46c-aab794fb7cbc'); SELECT UUID_DECODE(ID) FROM test; +--------------------------------------+ | uuid_decode(id) | +--------------------------------------+ | 323febe6cd89-4773-a46c-aab7-94fb7cbc | +--------------------------------------+ </code></pre> <p>I created a fiddle <a href="http://sqlfiddle.com/#!2/199db/2" rel="nofollow">here</a>.</p> <p>Note that:</p> <ul> <li>Although <code>CHARID</code> accepts NULLs, <code>ID</code> does not, so an attempt to insert a NULL value in <code>CHARID</code> would result in the trigger trying to set <code>ID</code> to NULL and the insertion being rejected.</li> <li>Attempting to insert an invalid value into <code>CHARID</code> that causes <code>UUID_ENCODE</code> to return NULL will also fail.</li> <li>The <code>DEFAULT 0</code> clause for <code>ID</code> is there only to allow you to omit the column from the insert list. The value written to the table will always be the one generated by your trigger.</li> <li>A nullable column that is always NULL should take between 0 and 2 bytes of additional storage per row depending on your table layout and row format (you would need to avoid for instance the <code>FIXED</code> format of the MyISAM engine).</li> <li>There are many variations possible. If you don't mind the extra storage, you can keep the <code>CHARID</code> value without setting it to NULL. If you want to allow explicit insert of binary <code>ID</code> values you can add a check to the trigger so you compute <code>NEW.ID</code> only if it is 0, etc.</li> <li>You should have a similar trigger for UPDATE operations, if they are allowed.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POIn MariaDB, function placed in a trigger starts returns a different value
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USrsanchez
    1. USrsanchez
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POIn MariaDB, function placed in a trigger starts returns a different value
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    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. VTBountyClose
    1. COThanks for pointing out the reason. Do I understand correctly that there is no possibility to use these `UUID_DECODE / UUID_ENCODE` functions, or, for example, `INET_NTOA / INET_ATON`, in the `BEFORE INSERT` trigger in such way as in my question?
      singulars
      1. PO
      1. USYuriy
    2. CO@Yuriy I've added an update to my answer.
      singulars
      1. PO
      1. USrsanchez
 

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