How to Filter and Execute Delete Statements Based on Binary Type Fields

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: Jellybean

[TiDB Usage Environment] Testing
[TiDB Version]
[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact]
I want to compare using the binary type field and then delete the matching records.
TiDB’s BINARY stores binary strings.

Steps to reproduce the problem:

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)

# Unable to delete, TiDB and MySQL 5.7 both cannot execute, TiDB and MySQL maintain consistent behavior
mysql> delete from t where bin_c=1;
ERROR 1292 (22007): Truncated incorrect INTEGER value: '1'

# TiDB cannot delete, MySQL 5.7 can execute normally, TiDB and MySQL behavior are inconsistent
mysql> delete from t where binary bin_c=1;
ERROR 1292 (22007): Truncated incorrect INTEGER value: '1'

# Invalid attempts in 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)
| username: 像风一样的男子 | Original post link

Try this: DELETE FROM t WHERE HEX(bin_c) = 1;

| username: Jellybean | Original post link

This doesn’t work either.

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)

| username: heiwandou | Original post link

There are many subtle differences between TiDB and MySQL.

| username: 像风一样的男子 | Original post link

Take a look at my operation.

| username: 像风一样的男子 | Original post link

This screenshot shows that the execution in MySQL is the same as in TiDB.

| username: Jellybean | Original post link

It seems that this conversion is necessary, thank you.

After several attempts, it can now be executed:

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>
| username: zhanggame1 | Original post link

Binary byte strings can be directly stored using varchar.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.