прелюдия
SQL пишется за секунды, а прибавка к зарплате кричит!
Новый стажер Сяо Ян написал оператор SQL
SELECT wx_id from `user` WHERE wx_id = 2
Когда Сяо Ян не мог дождаться, чтобы вернуться домой после работы, менеджер Ван по соседству схватил Сяо Ян и использовалEXPLAINОрден воспитал Сяо Ян, а Сяо Ян пролил слезы некультурности.
В этом операторе SQL wx_id имеет индекс, но результат, найденный менеджером Вангом, выглядит следующим образом.
Сяо Ян посмотрел поближе.keyПоле отображается как Null, очевидно, что этот оператор SQL не индексируется.
Сяо Ян подумал про себя: «Ой, я снова написал неверный оператор SQL, и теперь мне приходится сталкиваться со смешанными двойниками эксплуатации, обслуживания и менеджеров.
Нет, я должен немедленно изменить этот оператор SQL, дайте мне подумать, что пошло не так».
В голове у Сяо Яна лихорадочно закружилась голова, и когда он внимательно вспомнил структуру таблицы, то вдруг подумал, что поле wx_id имеет тип varchar, и при запросе он не ставил кавычки.
Сяо Ян схватил клавиатуру в руке менеджера и добавил кавычки к условию запроса wx_id.Конечно же, этот оператор SQL начал проходить через индекс. Сяо Ян доволен тем, что решил огромную ошибку.
Менеджер слегка улыбнулся и спросил: "Вы знаете, почему индекс остается после добавления кавычек? Если поле имеет тип int, нужно ли использовать кавычки при запросе? И почему?"
ужин приближается
Сяо Ян остался на месте, когда его спросили, не в силах ответить.
После исследования Сяо Яна было обнаружено, чтоЕсли поле имеет тип varchar, правая часть знака равенства должна быть заключена в кавычки для перехода в индекс; если поле имеет тип int, то правая часть знака равенства будет проиндексирована с кавычками или без них.
Какие? Вы не верите тому, что сказал Сяо Ян, есть фотографии и правда. (тип бонусного поля — int)
Но пришли к выводу, что он все еще не может ответить на три угрожающих жизни вопроса менеджера. Обеспокоенный Сяо Ян открыл публичный аккаунтКод Дуду ездитьХотите найти ответы.
Сяо Ян переместил ответ
Владелец Maerduduqi сказал Сяо Яну
В запросе MySQL неявное преобразование происходит, когда типы в левой и правой частях условия запроса не совпадают.
也就是说
SELECT wx_id from `user` WHERE wx_id = 2
等价于
SELECT wx_id from `user` WHERE CAST(wx_id AS signed int) = 2
Как только операция функции будет выполнена в поле индекса, MySQL откажется от использования индекса.
Таким образом, если поле имеет тип varchar, правая часть знака равенства должна быть заключена в кавычки перед индексом, иначе MySQL откажется от использования индекса из-за неявного преобразования. Так почему же int можно индексировать с кавычками или без них?
Это потому, что только 2 можно преобразовать в '2' для чисел типа int, что является единственной уверенностью. Таким образом, хотя требуется неявное преобразование, оно не влияет на использование индексов.
Сяо Ян спросил: «Можете ли вы еще рассказать мне о неявном знании преобразования?»
Основной бэкхенд - английский документ
If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
If both arguments in a comparison operation are strings, they are compared as strings.
If both arguments are integers, they are compared as integers.
Hexadecimal values are treated as binary strings if not compared to a number.
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
In all other cases, the arguments are compared as floating-point (real) numbers.
Пожалуйста, я помогу вам перевести его на китайский язык.
1, 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=>
对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
2, 两个参数都是字符串，会按照字符串来比较，不做类型转换
3, 两个参数都是整数，按照整数来比较，不做类型转换
4, 十六进制的值和非数字做比较时，会被当做二进制串
5, 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
6, 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数会将整数转换为 decimal 后进行比较，
如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
7, 所有其他情况下，两个参数都会被转换为浮点数再进行比较
Поделитесь еще одной ямой неявного преобразования
- Вы иногда удаляете некоторые данные, о которых вы не знаете?
mysql> select * from test;
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | test1 | password1 |
| 2 | test2 | password2 |
| 3 | aaa | aaaa |
| 4 | 55aaa | 55aaaa |
| 5 | 1212 | aaa |
| 6 | 1212a | aaa |
+----+-------+-----------+
6 rows in set (0.00 sec)
mysql> select * from test where name = 1212;
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 5 | 1212 | aaa |
| 6 | 1212a | aaa |
+----+-------+----------+
2 rows in set, 5 warnings (0.00 sec)
mysql> select * from test where name = '1212';
+----+------+----------+
| id | name | password |
+----+------+----------+
| 5 | 1212 | aaa |
+----+------+----------+
1 row in set (0.00 sec)
Приведенный выше пример предназначен для запроса записи с идентификатором 5, и в результате также запрашивается запись с идентификатором 6. Что я пытаюсь сказать? Иногда некоторые столбцы в нашей таблице базы данных имеют тип varchar, но сохраненное значение представляет собой чисто числовое строковое значение, такое как 1123. Некоторые студенты не привыкли добавлять кавычки при написании sql. Таким образом, при выполнении выбора, обновления или удаления можно манипулировать большим количеством данных. Так что не забывайте, где вы должны поставить кавычки.
в конце концов
Неявное преобразование типов имеет риск не попасть в индекс.В случае высокой параллелизма и большого объема данных последствия не попадания в индекс могут быть не только смешанные двойные действия со стороны эксплуатации и обслуживания и менеджера! и написать SQL и EXPLAIN
В этой статье используетсяmdniceнабор текста