Implizite Konvertierungsmethode in MySQL

隐式 转化 规则

官方 中 关于 隐式 隐式 转化 的 规则 是 描述 的 ： ： 🎜>

Wenn eines oder beide Argumente null sind, sind null, das Ergebnis des Vergleichs ist NULL, mit Ausnahme des NULL-sicheren <=> Gleichheitsvergleichsoperator. Für NULL <=> NULL, das Ergebnis ist wahr. Es ist keine Konvertierung erforderlich.

• Wenn beide Argumente in einer Vergleichsoperation Zeichenfolgen sind, werden sie als Zeichenfolgen verglichen.

• Wenn beide Argumente ganze Zahlen sind , werden sie als ganze Zahlen verglichen.

• Hexadezimale Werte werden als Binärzeichenfolgen behandelt, wenn sie nicht mit einer Zahl verglichen werden.

• Wenn eines der Argumente eine TIMESTAMP- oder DATETIME-Spalte ist und das andere Argument eine Konstante ist, wird die Konstante in einen Zeitstempel konvertiert, bevor der Vergleich durchgeführt wird. Dies geschieht, um ODBC-freundlicher zu sein. Beachten Sie, dass dies für die Argumente von IN() nicht erfolgt! Um auf der sicheren Seite zu sein, verwenden Sie bei Vergleichen immer vollständige datetime-, Datums- oder Zeitzeichenfolgen. Um beispielsweise bei der Verwendung von BETWEEN mit Datums- oder Zeitwerten die besten Ergebnisse zu erzielen, verwenden Sie CAST(), um die Werte explizit in den gewünschten Datentyp zu konvertieren.

  Eine einzeilige Unterabfrage aus einer oder mehreren Tabellen wird nicht als Konstante betrachtet. Wenn eine Unterabfrage beispielsweise eine Ganzzahl zurückgibt, die mit einem DATETIME-Wert verglichen werden soll, erfolgt der Vergleich als zwei Ganzzahlen. Die Ganzzahl wird nicht in einen Zeitwert umgewandelt. Um die Operanden als DATETIME-Werte zu vergleichen, verwenden Sie CAST(), um den Unterabfragewert explizit in DATETIME zu konvertieren.
  

• Wenn eines der Argumente ein Dezimalwert ist, hängt der Vergleich vom anderen Argument ab. Die Argumente werden als Dezimalwerte verglichen, wenn das andere Argument ein Dezimal- oder Ganzzahlwert ist, oder als Gleitkommawerte, wenn das andere Argument ein Gleitkommawert ist.

• In allen anderen In einigen Fällen werden die Argumente als Gleitkommazahlen (reelle Zahlen) verglichen
  两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换

两个参数都是字符串,会按照字符串来比较，不做类型转换

1. 两个参数都是整数，按照整数来比较，不做类型转换

2. 十六进制的值和非数字做比较时，会被当做二进制串

3. 有一个参数是TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp

4. 有一个参数是类型,如果另外一个参数是 decimal 或者整数会将整数转换为 dezimal 后进行比较, 如果另外一个参数是浮点数, 则会把 dezimal

5. 所有其他情况下，两个参数都会被转换为浮点数再进行比较

6. 问题描述

7. wo 条件语句里字段属性和赋给的条件，当数据类型不一样，这时候是没法直接比较的，需要进行一致转换

默认转换规则是：

不同类型全都转换为浮点型（下文都说成整型了，一个意思）
如果字段是字符, 条件是整型，那么会把表中字段全都转换为整型（也就是上面图中的问题，下面有详细解释）
转换总结
字符转整型

字符开头的一律为0

1. 数字开头的，直接截取到第一个不是字符的位置

• 时间类型转换

• 按照字符串进行截取

23:12:13 -> 2023-12-13（这个后文有讨论）

对于不符合的时间值，如10:12:32等，会变为. 0000-00-00或为 空

• cast函数只能转datetime，不能转timestamp

• 如果按照timestamp来理解，因为timestamp是有范围的('1970-01-01 00:00:01.000000' bis'2038-01-19. 03:14:07.999999')，所以只能是2023年，而不能是1923年

• 直接截取time字段

• 直接截取date字段

• 无意义，直接为 00:00:00

• 00:00:00 anhängen

• Datum an Datum/Uhrzeit oder Zeitstempel anhängen

• Datum zu Uhrzeit

• DatumUhrzeit oder Zeitstempel zu Datum

• DatumUhrzeit oder Zeitstempel zu Datum

• Zeit zu Datum/Uhrzeit oder Zeitstempel

• Wenn Zeit und Datum/Uhrzeit in Zahlen umgewandelt werden, erhalten sie die doppelte Genauigkeit plus ms (verschiedene Versionen)

Fallanalyse

• Tabellenstruktur, das Namensfeld hat einen Index

-- 注意name字段是有索引的CREATE TABLE `t3` (  `id` int(11) NOT NULL,  `c1` int(11) NOT NULL,  `name` varchar(100) NOT NULL DEFAULT 'fajlfjalfka',  KEY `name` (`name`),  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

-- 模拟线上一个隐式转换带来的全表扫面慢查询-- 发生隐式转换
xxxx.test> select * from t3 where name = 0;
+----+----+-------------+
| id | c1 | name        |
+----+----+-------------+
|  1 |  2 | fajlfjalfka |
|  2 |  0 | fajlfjalfka |
|  1 |  2 | fajlfjalfka |
|  2 |  0 | fajlfjalfka |
+----+----+-------------+
4 rows in set, 4 warnings (0.00 sec)-- 上述SQL执行计划是全表扫描，扫描后，字符转整型，都是0，匹配上了条件，全部返回
xxxx.test> desc select * from t3 where name = 0;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t3    | ALL  | name          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)-- 加上单引号后，是走name索引的，非全表扫描
xxxx.test> desc select * from t3 where name = '0';
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t3    | ref  | name          | name | 102     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)-- 走索引，没返回
xxxx.test>  select * from t3 where name = '1';
Empty set (0.00 sec)

Erklärung

• Wenn die Bedingung als 0 oder 1 geschrieben wird, wird die gesamte Tabelle gescannt. Alle Namensfelder müssen von Zeichen in Ganzzahlen umgewandelt und dann mit 0 oder 1 verglichen werden. Da es sich bei allen um Zeichen handelt, die mit einem Buchstaben beginnen, werden sie alle in 0 konvertiert und das zurückgegebene Ergebnis besteht aus allen Zeilen.

• Dann fragte jemand, warum man das 0 in der Bedingung nicht automatisch in '0' ändern würde? siehe unten.

Konvertierungsbeispiel

-- 字符开头，直接是0
xxxx.test> select cast('a1' as unsigned int) as test ;
+------+
| test |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

xxxx.test> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a1' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)-- 开头不是字符，一直截取到第一个不是字符的位置
xxxx.test> select cast('1a1' as unsigned int) as test ; 
+------+
| test |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

xxxx.test> select cast('123a1' as unsigned int) as test ;
+------+
| test |
+------+
|  123 |
+------+
1 row in set, 1 warning (0.00 sec)-- 直接按照字符截取，补上了20（不能补19）
xxxx.test> select cast('23:12:13' as datetime) as test ;
+---------------------+
| test                |
+---------------------+
| 2023-12-13 00:00:00 |
+---------------------+
1 row in set (0.00 sec)-- 为什么不能转换为timestamp，没搞清楚，官方文档给的转换类型里没有timestamp。如果是这样的话，上面的datetime就不好解释为什不是1923了。难道是检测了当前的系统时间？
xxxx.test> select cast('23:12:13' as timestamp) as test ;    
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'timestamp) as test' at line 1-- 这个时间无法转换成datetime
xxxx.test> select cast('10:12:32' as datetime) as test ;         
+------+
| test |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

xxxx.test> show warnings ;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: '10:12:32' |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)-- 5.5版本下，时间转字符，会增加ms
xxxx.(none)> select version();
+------------+
| version()  |
+------------+
| 5.5.31-log |
+------------+
1 row in set (0.00 sec)

xxxx.(none)> select CURTIME(), CURTIME()+0, NOW(), NOW()+0 ;
+-----------+---------------+---------------------+-----------------------+
| CURTIME() | CURTIME()+0   | NOW()               | NOW()+0               |
+-----------+---------------+---------------------+-----------------------+
| 15:40:01  | 154001.000000 | 2016-05-06 15:40:01 | 20160506154001.000000 |
+-----------+---------------+---------------------+-----------------------+
1 row in set (0.00 sec)-- 5.6 不会
xxxx.test> select version();
+------------+
| version()  |
+------------+
| 5.6.24-log |
+------------+
1 row in set (0.00 sec)

xxxx.test> select CURTIME(), CURTIME()+0, NOW(), NOW()+0 ;
+-----------+-------------+---------------------+----------------+
| CURTIME() | CURTIME()+0 | NOW()               | NOW()+0        |
+-----------+-------------+---------------------+----------------+
| 15:40:55  |      154055 | 2016-05-06 15:40:55 | 20160506154055 |
+-----------+-------------+---------------------+----------------+
1 row in set (0.00 sec)

Warum nicht das where name = 0 in 0 in '0' umwandeln?

• Wenn Sie Zahlen in Zeichen umwandeln, z. B. 0 in „0“, kann das Abfrageergebnis nur sein, dass das Feld gleich „0“ ist, aber tatsächlich sind die Daten in Die Tabelle, zum Beispiel „a0“, „00“, sind tatsächlich die 0, die der Benutzer haben möchte. Schließlich hat der Benutzer die Zahl 0 angegeben, sodass MySQL immer noch die Anforderung des Benutzers als Standard verwendet. wird nicht an den Benutzer zurückgegeben.

Zusammenfassung

• Mit dem obigen Inhalt kann die Frage am Anfang erklärt werden.

• Kann das obige Beispiel verwendet werden, um die Authentifizierung zu umgehen?

Ergänzung

-- 上面遗留的问题，跟系统时间并没有关系。怀疑虽然指定的是datetime，但是内部还是按照timestamp去做的。
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 1999-08-03 14:16:50 |
+---------------------+
1 row in set (0.00 sec)

mysql> select cast('23:12:13' as datetime) as test ;
+---------------------+
| test                |
+---------------------+
| 2023-12-13 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

Das obige ist der detaillierte Inhalt vonImplizite Konvertierungsmethode in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!