Converting from VARCHAR to BINARY(16) in MySQL?-CodePudding

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)?

CodePudding user response：

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]>

CodePudding user response：

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;