Autogenerate UUID in Liquibase - Mysql MariaDB

Viewed 5021

I was trying to create a table whose primary key should be an autogenerated UUID on isertion.

mysql --version
mysql  Ver 15.1 Distrib 10.1.22-MariaDB, for osx10.11 (x86_64) using readline 5.1

This is the changeset I have tried :

   <changeSet author="shalinlk" id="201705161522">
    <createTable tableName="quiz_question">
        <column valueComputed="UUID()" name="id" type="CHAR(36)">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="quiz_id" type="CHAR(36)">
            <constraints nullable="true" foreignKeyName="fk_quiz_question_quiz_id"
                         references="quiz(id)"/>
        </column>
        <column name="question_id" type="CHAR(36)">
            <constraints nullable="true" foreignKeyName="fk_quiz_question_question_id"
                         references="question(id)"/>
        </column>
    </createTable>
</changeSet>

But this is not even treating the valueComputed property in the corresponding query generated. Can anyone help please.

Note : the query

select UUID();

is working correctly when I tried in mysql terminal.

Edit : The query generated is

CREATE TABLE quiz_question (
    id CHAR(36), 
    quiz_id CHAR(36) NULL, 
    question_id CHAR(36) NULL, 
    CONSTRAINT PK_QUIZ_QUESTION PRIMARY KEY (id), 
    CONSTRAINT fk_quiz_question_question_id FOREIGN KEY (question_id) 
    REFERENCES question(id), 
    CONSTRAINT fk_quiz_question_quiz_id FOREIGN KEY (quiz_id) REFERENCES quiz(id))

I have removed fields that are not relevant.

Edit : Will be helpful if anyone can point out some reference/example on this.

2 Answers

If you're about to store UUIDs in mysql/mariadb, please don't use varchar/char fields. They are just a 128-bit binary values, outputted with a nice block separation and hex formatted.

Using CHAR(36), which is probably using an utf8-encoded charset and uses at least 3 Bytes per character, you'd need 36*3 = 108 Bytes to store each UUID.

Instead, you can use a BINARY(16) field to store the underlying value (=16 Bytes...). Sadly, mysql doesn't do that out of the box. See http://mysql.rjweb.org/doc.php/uuid for a deeper explanation and more information how to convert uuids in mysql.

Related