MySQL 隱式轉換

?

字段類型定義

CREATE TABLE `user` (`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '編號',/* ...... */`name` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名',/* ...... */PRIMARY KEY (`id`),KEY `idx_name` (`name`,`nickname`),/* ...... */
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

?

索引類型注意

字段類型為“字符串”

• 查詢：數字

mysql> EXPLAIN SELECT * FROM user WHERE name=123;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_name      | NULL | NULL    | NULL | 21541 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

• 查詢：字符串

mysql> EXPLAIN SELECT * FROM user WHERE name='123';
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name      | idx_name | 32      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

對比結果

當字段類型是varchar等字符類時，如果查詢值是數字類型，索引不生效。

?

字段類型為“數字”

• 查詢：字符串

mysql> EXPLAIN SELECT * FROM user WHERE id='12';
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

• 查詢：數字

mysql> EXPLAIN SELECT * FROM user WHERE id=12;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

對比結果

字符串會被轉為整數處理，所以對定義為整數類型的字段而言，索引都可以用到；

說明

只有字段類型和查詢的字段值類型對應時，才可用。

---

?

隱式轉換

mysql>  SELECT 'a'+123, 's'+'234', 5+'ab', 66+'kj', CONCAT(56, 'go');
+---------+-----------+--------+---------+------------------+
| 'a'+123 | 's'+'234' | 5+'ab' | 66+'kj' | CONCAT(56, 'go') |
+---------+-----------+--------+---------+------------------+
|     123 |       234 |      5 |      66 | 56go             |
+---------+-----------+--------+---------+------------------+
1 row in set, 4 warnings (0.00 sec)

說明

MySQL會將數字和字符串的直接相加，
轉為整數型；可以使用CONCAT函數轉為字符串類型；

隱式轉化把字符串轉為了整數類型。但是因為字符串是非數字型的，所以就會被轉換為0

---

?

MySQL文檔說明

When an operator is used with operands of different types, type conversion occurs to make the operands compatible.當操作符與不同類型的操作數一起使用時，將進行類型轉換以使操作數兼容。

?