"Pit" in MySQL, string and numeric comparisons

Official document:https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html

Original:

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These operations work for both numbers and strings. Strings is automatically converted to numbers and numbers to Strings as necessary.

In other words, a string may be converted to a number when compared.

For a string at the beginning of the data, a number is automatically discarded, leaving only the pure digits to be compared.

For those strings that do not have numbers, when compared to the values, only 0 is left to compare with the other values.

Example:

1. for those strings that do not have numbers, when compared to the values, only 0 is left to compare with the other values. :

Root [(none)] >select 0= ' abc ';

+---------+

| 0= ' abc ' |

+---------+

| 1 |

+---------+

1 row in Set, 1 Warning (0.00 sec)

Root [(none)] >show warnings;

+---------+------+-----------------------------------------+

| Level | Code | Message |

+---------+------+-----------------------------------------+

| Warning | 1292 | Truncated incorrect DOUBLE value: ' abc ' |

+---------+------+-----------------------------------------+

1 row in Set (0.00 sec)

2, for the data at the beginning of the string, converted into a number will automatically discard the following letter part, leaving only pure numbers to compare.

Root [(none)] >select 11= ' 010abc ';

+-------------+

| 11= ' 010ABC ' |

+-------------+

| 0 |

+-------------+

1 row in Set, 1 Warning (0.00 sec)

Root [(none)] >show warnings;

+---------+------+--------------------------------------------+

| Level | Code | Message |

+---------+------+--------------------------------------------+

| Warning | 1292 | Truncated incorrect DOUBLE value: ' 010abc ' |

+---------+------+--------------------------------------------+

1 row in Set (0.00 sec)

The official website gives examples more, more interested can go to see.

In fact, the biggest hole in string and numeric values is that it causes queries not to be indexed and directly affects the efficiency of queries.

"Pit" in MySQL, string and numeric comparisons