Optimally storing Guid in MySQL: "Data too long for column" even if it should fit to CHAR(16)

Question

I'm trying to get the most optimized method for storing a GUID as the primary key, which is Binary(16) BUT be able to look at the binary value as text in a MySQL Workbench query. MSSQL makes this easy, why not MySQL?

I have created a table called "hosts" where the primary key is a CHAR(16) called "SystemID". Looking at others example not sure whats wrong.

Why am I getting the error:

Data too long for column 'SystemID' at row 1

My test ASP.NET method looks like:

[HttpGet("add")]
        public ActionResult<string> Add([FromQuery(Name = "systemid")] Guid systemID)
        {
            //https://localhost:44344/api/hostresolver/add?systemid=D3DDCAC9-7DAD-44DF-AD5E-7F9F10AA0EF3

            using (var connection = new MySqlConnection(DBSettings.connectionString))
            {
                try
                {
                    connection.Open();

                    var data = new byte[255];
                    string sql = "INSERT INTO hosts VALUES(?SystemID, ?TrackingID)";
                    using (var cmd = new MySqlCommand(sql, connection))
                    {
                        cmd.Parameters.Add("?SystemID", MySqlDbType.Guid).Value = systemID;
                        cmd.Parameters.Add("?TrackingID", MySqlDbType.Binary, 255).Value = data;
                        return rowsEffected != 0 ? "Success" : "Failed";
                    }
                }
                catch (Exception e)
                {
                    return e.Message;
                }

                connection.Close();
            }

            return "Failed";
        }

Since Guid.NewGuid().ToByteArray() produces 16 bytes I expect it to fit to CHAR(16).

Changing the key to MySqlDbType.Binary and setting its value to systemID.ToByteArray() works but I can't see what the SystemID value is when doing a query in MySQL Workbench. I should be able to use CHAR(16) but its not working.

Maybe I should to use: "BIN_TO_UUID"?

If your column is CHAR(16), you can't use a `Guid systemID`. Guid is 36 chars long, or 32 in shortform (.ToString("N")). — zaitsman, Dec 19 '18 at 03:00
Guid is 16 bytes long. You can confirm this by doing Guid.NewGuid().ToByteArray() — zezba9000, Dec 19 '18 at 03:02
@JohnB `System.Guid` is exactly 16 bytes. The problem here is that when you try to save GUID as CHAR(16) MySQL will use string representation, which is 36 characters in length — vasily.sib, Dec 19 '18 at 03:02
Also worth to read this: [How should I store GUID in MySQL tables?](https://stackoverflow.com/questions/412341/how-should-i-store-guid-in-mysql-tables). You can follow the solutions provided there to store GUID values as CHAR(n). — Tetsuya Yamamoto, Dec 19 '18 at 03:04
Is CHAR(16) the recommended way to store a GUID in MySQL. I didn't see UUID options like I do in MSSQL. — zezba9000, Dec 19 '18 at 03:05
maybe [this post](https://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/) can help. But as I think, as Guid is 16 bytes (not 16 characters) you should store it as `BINARY(16)`. — vasily.sib, Dec 19 '18 at 03:20
@zezba9000 please [*do not* keep history](https://meta.stackoverflow.com/questions/255644/should-edit-in-edits-be-discouraged) of your changes in the post. I tried to edit them out - double check if that aligns with what you are asking/trying. — Alexei Levenkov, Dec 19 '18 at 03:53

score 3 · Accepted Answer · answered Dec 19 '18 at 17:06

3

By default, Connector/NET stores GUIDs as CHAR(36). If you want to store them as 16 bytes, you need to change the column type to BINARY(16) and set OldGuids=true in your connection string.

From Connection Options:

The back-end representation of a GUID type was changed from BINARY(16) to CHAR(36). This was done to allow developers to use the server function UUID() to populate a GUID table - UUID() generates a 36-character string.

Note that if you do this, all BINARY(16) columns in your database will be read back as System.Guid. If this isn't desirable, you should stick with the default CHAR(36) approach.

If you need more flexibility, MySqlConnector (an alternate ADO.NET MySQL Library) supports a GuidFormat option, including Char36, Binary16, and LittleEndianBinary16.

answered Dec 19 '18 at 17:06

Bradley Grainger

27,458
4
91
108

Correct me if I'm wrong but this CHAR(36) is a horrible approach (as its much slower) vs MSSQL which simply has a proper 16 byte GUID column type? The meta-data / type-system in MySQL seems wonky as hell or super dated. Just going to stick with BINARY(16) for performance and use BIN_TO_UUID in my queries. – zezba9000 Dec 19 '18 at 18:54
@zezba9000 Correct: MySQL has no built-in `GUID` column type; you have to pick `CHAR(36)` (Connector/NET default) or `BINARY(16)`, then set the `OldGuids` connection string option appropriately. – Bradley Grainger Dec 19 '18 at 19:00
1

@zezba9000 "The meta-data / type-system in MySQL seems wonky as hell or super dated." Yep. There is no `BOOL` column type; you have to use `TINYINT(1)`. The `utf8` character set can't store Unicode text; you have to use `utf8mb4`. The default Unicode collation is pretty dated. All binary data is sent to/from MySQL as hex characters, not as binary. There's probably a lot more. – Bradley Grainger Dec 19 '18 at 19:02
I've decided to go with PostgresDB. Seems to be the best option in general. – zezba9000 Dec 19 '18 at 23:21

Gauravsa · Answer 2 · 2018-12-19T03:19:51.137

0

You can change the datatype to LONGTEXT or even better char(16) or even binary(16).

edited Dec 19 '18 at 03:19

answered Dec 19 '18 at 03:16

Gauravsa

6,330
2
21
30

why can't you do char(16)? Both binary and char should both represent the same size correct? – zezba9000 Dec 19 '18 at 03:18
the question was exactly about the fact, that CHAR(16) is not enough – vasily.sib Dec 19 '18 at 03:21
i usually go for char(36). little bit of wastage is fine with me. – Gauravsa Dec 19 '18 at 03:22
it is more then twice of wastage. Also, sorting of `CHAR(36)` is slower, then sorting `BINARY(36)` because your server need to check for collation. – vasily.sib Dec 19 '18 at 03:24
for indexing and fast searching? Do you know, that primary key is **always** indexed? – vasily.sib Dec 19 '18 at 03:28
To be clear, I'm not a downvoter for this (and I don't know why is this answer is downvoted), because `LONGTEXT`, `CHAR(36)` and `BINARY(16)` actually solves the question asked. I just in doubt is this a **good** (in sense of "good practise") answer? – vasily.sib Dec 19 '18 at 03:41

Optimally storing Guid in MySQL: "Data too long for column" even if it should fit to CHAR(16)

2 Answers2