如何根据binary类型字段过滤执行delete语句

[Jellybean (李文杰)]

【 TiDB 使用环境】测试
【 TiDB 版本】
【复现路径】
【遇到的问题：问题现象及影响】
想要通过binary类型字段进行比较，然后符合条件的删除。
TiDB的 BINARY 存储的是二进制字符串。

问题复现步骤：
CREATE TABLE t (
id int(11) DEFAULT NULL,
bin_c binary(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

mysql> insert into t values (1,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t where bin_c=1;
±-----±-----------------------------------------------------------------+
| id | bin_c |
±-----±-----------------------------------------------------------------+
| 1 | 1 |
±-----±-----------------------------------------------------------------+
1 row in set (0.00 sec)

#无法删除，TiDB和 MySQL5.7 同样无法执行，TiDB 与 MySQL保持一致的行为
mysql> delete from t where bin_c=1;
ERROR 1292 (22007): Truncated incorrect INTEGER value: '1

#TiDB 无法删除，MySQL 5.7 可以正常执行，TiDB 与 MySQL的行为不一致
mysql> delete from t where binary bin_c=1;
ERROR 1292 (22007): Truncated incorrect INTEGER value: '1

#TiDB里无效的尝试方式
mysql> delete from t where bin_c=cast(1 as binary);
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t where bin_c=binary(1);
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘sql_mode’;
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[像风一样的男子]

delete from t where hex( bin_c)=1; 试试这个

[Jellybean (李文杰)]

这个也不行。

mysql> select * from t ;
±-----±-----------------------------------------------------------------+
| id | bin_c |
±-----±-----------------------------------------------------------------+
| 1 | 1 |
±-----±-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> delete from t where hex( bin_c)=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t ;
±-----±-----------------------------------------------------------------+
| id | bin_c |
±-----±-----------------------------------------------------------------+
| 1 | 1 |
±-----±-----------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> explain delete from t where hex( bin_c)=1;
±--------------------------±--------±----------±--------------±----------------------------------------------+
| id | estRows | task | access object | operator info |
±--------------------------±--------±----------±--------------±----------------------------------------------+
| Delete_4 | N/A | root | | N/A |
| └─TableReader_8 | 0.80 | root | | data:Selection_7 |
| └─Selection_7 | 0.80 | cop[tikv] | | eq(cast(hex(test.t.bin_c), double BINARY), 1) |
| └─TableFullScan_6 | 1.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
±--------------------------±--------±----------±--------------±----------------------------------------------+
4 rows in set (0.00 sec)

[heiwandou (黑豌豆)]

tidb与mysql有好多细微差异呀

[像风一样的男子]

你看下我的操作

[像风一样的男子]

这个截图是mysql执行的和tidb一样的

[Jellybean (李文杰)]

看来是需要这样转换才可以，感谢大佬。

需要几次兜兜转转，现在是可以执行了：
mysql> select hex(bin_c) from t ;
±---------------------------------------------------------------------------------------------------------------------------------+
| hex(bin_c) |
±---------------------------------------------------------------------------------------------------------------------------------+
| 31000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
±---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t where hex(bin_c)=‘31000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000’ ;
±-----±-----------------------------------------------------------------+
| id | bin_c |
±-----±-----------------------------------------------------------------+
| 1 | 1 |
±-----±-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> delete from t where hex(bin_c)=‘31000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000’ ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t ;
Empty set (0.00 sec)

mysql>

[zhanggame1 (Ti D Ber G I13ecx U)]

二进制byte字符串可以直接用varchar存