MySql 插入语句到二进制数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9081216/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql insert statement to binary datatype?
提问by DHRUV BANSAL
I am using MySQL database.
我正在使用 MySQL 数据库。
I have one table having column with datatype binary(16).
我有一个包含数据类型为 binary(16) 的列的表。
I need help with the insert statement for this table.
我需要有关此表的插入语句的帮助。
Example:
CREATE TABLE `assignedresource` (
`distid` binary(16) NOT NULL
)
insert into assignedresource values ('9fad5e9e-efdf-b449');
Error : Lookup Error - MySQL Database Error: Data too long for column 'distid' at row 1
How to resolve this issue?
如何解决这个问题？
回答by Rik Smith-Unna
You should remove the hyphens to make the value match the length of the field...
您应该删除连字符以使值与字段的长度匹配...
Example:
CREATE TABLE `assignedresource` (
`distid` binary(16) NOT NULL
)
insert into assignedresource values ('9fad5e9eefdfb449');
Also, MySQL standard is to use this notation to denote the string as binary... X'9fad5e9eefdfb449', i.e.
此外，MySQL 标准是使用这种表示法将字符串表示为二进制... X'9fad5e9eefdfb449'，即
insert into assignedresource values (X'9fad5e9eefdfb449');
回答by Xedret
Well, assuming that you want to strictly insert a hexadecimal string, first you need to remove the dashes and then "unhex"your string before inserting it into a binary(16) data type column, the code would go like this:
好吧，假设你想严格插入一个十六进制字符串，首先你需要删除破折号，然后在将它插入到 binary(16) 数据类型列之前“unhex”你的字符串，代码将是这样的：
INSERT INTO `assignedresource` VALUES(UNHEX(REPLACE('9fad5e9e-efdf-b449','-','')));
Also... the "usable"data you are inserting is actually 8 bytesafter undashingit, so binary(8)would do fine if you plan on not storing the dashes.
此外...您插入的“可用”数据实际上是在取消破折号后8 个字节，因此如果您计划不存储破折号，binary(8)会很好。
回答by Rene Pot
Your string is 18 char long, change the database
您的字符串长度为 18 个字符，请更改数据库
CREATE TABLE `assignedresource` (
`distid` binary(18) NOT NULL
)
