Using MySQL 5.5.60.
I'm running into some peculiar behavior when running select queries in Mysql. I have a table list whose schema looks like this:
+-----------------------+--------------+------+-----+-----------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+-----------------+----------------+
| list_id | int(11) | NO | PRI | NULL | auto_increment |
| vendor_id | int(11) | NO | MUL | NULL | |
| referrer_id | int(11) | NO | | 0 | |
...
If I run this query
mysql> select * from list where list_id = "1946"\G
Everything works as it should and the list with id 1946 is returned. Here is where it gets weird. If I change my query to look like this:
mysql> select * from list where list_id = "1946dhkdf"\G
It still returns list 1946! Clearly MySQL somehow cast off the dhkdf part and uses the 1946 portion only. So does it try to cast that value to an Integer that way? Why then does this query return and empty set?
mysql> select * from list where list_id = "xq1946dhkdf"\G
I can't seem to find any documentation explaining this behavior. Can someone shed some light on it?
You are seeing MySQL's somewhat complex casting rules at work here. When trying to compare an integer column against a string literal, either one has to be cast to integer, or the other to string. In this case, MySQL will try to cast the string literal to an integer, to match the type of the column. But, in this case, it can't cast the entire string literal to an integer, since it contains characters. Therefore, the casting rules kick in, which state that if the first N characters of the string be numeric, then use only that leading number. So, as a result the following query:
select * from list where list_id = "1946dhkdf";
will return the same result set as:
select * from list where list_id = "1946";
Эта статья взята из Интернета, укажите источник при перепечатке.
Если есть какие-либо нарушения, пожалуйста, свяжитесь с[email protected] Удалить.
я говорю два предложения