CODEWITHSUNDEEP
sqldatabaseuuidtruncate
MParsons
MParsons

Reputation: 289

SQL Truncate Error when Inserting UUID

I'm building a database driven PHP application that uses UUID's to store each row of data. The application currently generates the UUID's using the following query:

    SELECT UUID()

which generates an output similar to 84058227-294c-11e3-916a-7a7919b2b2bc.

Many online sources suggest that UUID's be stored in BINARY(16) to improve overall application performance. But when I attempt to insert the UUID into the database, I receive the following error:

    Warning: #1265 Data truncated for column 'x' at row x

I realize that this error occurred because the UUID was too large for the column to store, and that this issue can be easily fixed by simply increasing the amount of characters the column may store (ex: BINARY(20)), but I fear that doing so may reduce the application's performance in the future.

Considering that so many online sources suggest using BINARY(16) to store UUID's, I'm assuming that I have made a mistake.

Could someone please point me in the right direction?

For extra information, here is the code I'm using (in PHP) to insert data into the database:

    //PDO Query
    $this->query(
        INSERT INTO users
            (
                user_id,        //the UUID is stored in this column
                taxonomy_id,
                user_email,
                user_password,
                user_salt,
                user_activation_key,
                user_is_administrator
            )
            VALUES(?,?,?,?,?,?,?)
            ',
            array(
                $this->uuid(),    //method that generates UUID
                $taxonomy_id,
                $user_email,
                $user_password,
                $user_salt,
                $user_activation_key,
                $user_is_administrator
            )
        )
    );

and the method that generates each UUID:

    public function uuid()
    {
        $uuid = $this->query("SELECT UUID() AS uuid");
        return $uuid[0]['uuid'];
    }

Upvotes: 1

Views: 2529

Answers (1)

Chris Johnson
Chris Johnson

Reputation: 21936

The underlying value of a UUID is a 128-bit number, which can be stored as a binary(16) since 8*16 >= 128. However the built-in SQL function UUID you're using (assuming MySQL ... you didn't name the RDBMS) renders the 128-bit number as a 36-character string, which is the normal human-readable form of a UUID. So you will need to store it in a text field of length >= 36, such as a varchar(36).

Unless your application is going to generate a vast amount of data and queries, the difference between 16 bytes in a binary field vs. 36 bytes in a character field won't affect your performance. Other issues like indexing and proper query design (and especially hinting if you are using MySQL) will have a much greater effect on your overall performance.

Upvotes: 1

Related Questions