Converting from VARCHAR to BINARY(16) in MySQL?

Question

I have as table with this column and type:

sales_channel_id BINARY(16)

In the row I have this value: 5DBACA1114B24872ACCFE679037DF670

I have written this value into another table, but this time the table column has the type VARCHAR(255). In the table I see this value: 5dbaca1114b24872accfe679037df670 (no capitals).

Now I have created another table with a column of type BINARY(16). When I make something like this to transform the data from the varchar column to the new column like this:

INSERT INTO 
setting_sales_channel (sales_channel_id)
SELECT sales_channel_id from mcn_setting

I get the error: Query 1 ERROR: Data too long for column 'sales_channel_id' at row 1

Why does this happen and how can I transfer the data from the VARCHAR column into the new column which is of type BINARY(16)?

Can you replicate a sample of your environment (and your output error) in a fiddle site like db-fiddle.com? — lemon
– lemon, Commented Jul 1, 2022 at 19:32
You can see it here with a simple insert: db-fiddle.com/f/hXbFdM9P4TNuC1mibaXagR/0 — Torben
– Torben, Commented Jul 1, 2022 at 19:38
@Torben Do you want to use UNHEX()? What you have is a 32 character long string, which will not fit into a 16 bytes long column. — Progman
– Progman, Commented Jul 1, 2022 at 19:42
I don't know how many things like this you have to do, or how often, but if you want to do it with the difficulty curve of an Excel file, download and install Microsoft Workbench, and you can do ALTER commands with right clicks ... — easleyfixed
– easleyfixed, Commented Jul 1, 2022 at 19:43

Bernd Buffen · Accepted Answer · 2022-07-01 19:45:00Z

Try this.

change your query to:

INSERT INTO 
setting_sales_channel (sales_channel_id)
SELECT CONVERT(sales_channel_id,BINARY(16)) from mcn_setting;

sample (MariaDB)

MariaDB [bernd]> show create table b;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| b     | CREATE TABLE `b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bbinary` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [bernd]> show create table bs;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bs    | CREATE TABLE `bs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bvarchar` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [bernd]> SELECT * from b;
Empty set (0.002 sec)

MariaDB [bernd]> SELECT * from bs;
+----+----------------------------------+
| id | bvarchar                         |
+----+----------------------------------+
|  1 | 5dbaca1114b24872accfe679037df670 |
+----+----------------------------------+
1 row in set (0.000 sec)

MariaDB [bernd]> 
MariaDB [bernd]> INSERT INTO b (bbinary) 
    -> SELECT CONVERT(bvarchar,BINARY(16))  FROM bs;
Query OK, 1 row affected, 1 warning (0.003 sec)
Records: 1  Duplicates: 0  Warnings: 1

MariaDB [bernd]> SELECT * from bs;
+----+----------------------------------+
| id | bvarchar                         |
+----+----------------------------------+
|  1 | 5dbaca1114b24872accfe679037df670 |
+----+----------------------------------+
1 row in set (0.000 sec)

MariaDB [bernd]>

Then I get this error: Query 1 ERROR: Truncated incorrect BINARY(16) value: '5dbaca1114b24872accfe679037df670'

Torben · Accepted Answer · 2022-07-01 19:54:27Z

1

In my case it worked when using UNHEX now. The result looked like this:

INSERT INTO setting_sales_channel (sales_channel_id)
SELECT UNHEX(sales_channel_id) from setting;

answered Jul 1, 2022 at 19:54

Torben

5,53413 gold badges53 silver badges84 bronze badges

Collectives™ on Stack Overflow

Converting from VARCHAR to BINARY(16) in MySQL?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related