MySQL 隱式型別轉換

官網： https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

create table t_base_user(
oid bigint(20) not null primary key auto_increment,
name varchar(30) null comment "name",
email varchar(30) null comment "email",
age int null comment "age",
telephone varchar(30) null comment "telephone",
status tinyint(4) null comment "0 無效 1 有效",
created_at datetime null default now() comment "建立時間",
updated_at datetime null default now() comment "修改時間" );

### 新建索引
alter table t_base_user add index idx_oid(oid);
alter table t_base_user add index idx_email(email);
alter table t_base_user add index idx_name(name);
alter table t_base_user add index idx_telephone(telephone);
alter table t_base_user add index idx_telephone_email(telephone,email);
alter table t_base_user add index idx_id_name(oid,name);

alter table t_base_user add index idx_id_created_at(oid,created_at);
alter table t_base_user add unique index idx_id_updated_at(oid,updated_at);
alter table t_base_user add unique index idx_oid2(oid);

### 新增記錄:
INSERT INTO `t_base_user` (`name`, `email`, `age`, `telephone`, `status`, `created_at`, `updated_at`)
VALUES ('111111', 'andytohome@gmail.com', '111', '12345678901', '1', now(),now());

insert into t_base_user(name,email,age,telephone,status) select name,email,age,telephone,status from t_base_user;

### 查詢
explain select * from t_base_user where telephone=12345678901;

explain select * from t_base_user where telephone='12345678901';

explain select * from t_base_user where telephone=cast(12345678901 as char);

explain select * from t_base_user where telephone=cast(12345678901 as char charset latin1);

explain select * from t_base_user where oid='1';
explain select * from t_base_user where oid=1;

--------------------------------------------- Oracle
create table t_base_user(
oid NUMBER(20) not null primary KEY ,
name VARCHAR2(30) ,
email VARCHAR2(30) ,
telephone VARCHAR2(30) );

CREATE INDEX idx_telephone ON t_base_user(telephone);

INSERT INTO t_base_user(oid, name,email,telephone)
VALUES ('111111', 'lhrbest@gmail.com', '111', '12345678901');

set linesize 1000
set autot on
select * from t_base_user where telephone=12345678901;
select * from t_base_user where telephone='12345678901';

---------------------------------------------------------------------------------
mysql> explain select * from t_base_user where telephone=12345678901;
+----+-------------+-------------+------------+------+-----------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------------------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t_base_user | NULL | ALL | idx_telephone,idx_telephone_email | NULL | NULL | NULL | 521550 | 10.00 | Using where |
+----+-------------+-------------+------------+------+-----------------------------------+------+---------+------+--------+----------+-------------+
1 row in set, 5 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'idx_telephone' due to type or collation conversion on field 'telephone' |
| Warning | 1739 | Cannot use ref access on index 'idx_telephone_email' due to type or collation conversion on field 'telephone' |
| Warning | 1739 | Cannot use range access on index 'idx_telephone' due to type or collation conversion on field 'telephone' |
| Warning | 1739 | Cannot use range access on index 'idx_telephone_email' due to type or collation conversion on field 'telephone' |
| Note | 1003 | /* select#1 */ select `lhrdb`.`t_base_user`.`oid` AS `oid`,`lhrdb`.`t_base_user`.`name` AS `name`,`lhrdb`.`t_base_user`.`email` AS `email`,`lhrdb`.`t_base_user`.`age` AS `age`,`lhrdb`.`t_base_user`.`telephone` AS `telephone`,`lhrdb`.`t_base_user`.`status` AS `status`,`lhrdb`.`t_base_user`.`created_at` AS `created_at`,`lhrdb`.`t_base_user`.`updated_at` AS `updated_at` from `lhrdb`.`t_base_user` where (`lhrdb`.`t_base_user`.`telephone` = 12345678901) |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> explain select * from t_base_user where telephone='12345678901';
+----+-------------+-------------+------------+------+-----------------------------------+---------------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------------------------+---------------+---------+-------+--------+----------+-------+
| 1 | SIMPLE | t_base_user | NULL | ref | idx_telephone,idx_telephone_email | idx_telephone | 33 | const | 260775 | 100.00 | NULL |
+----+-------------+-------------+------------+------+-----------------------------------+---------------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `lhrdb`.`t_base_user`.`oid` AS `oid`,`lhrdb`.`t_base_user`.`name` AS `name`,`lhrdb`.`t_base_user`.`email` AS `email`,`lhrdb`.`t_base_user`.`age` AS `age`,`lhrdb`.`t_base_user`.`telephone` AS `telephone`,`lhrdb`.`t_base_user`.`status` AS `status`,`lhrdb`.`t_base_user`.`created_at` AS `created_at`,`lhrdb`.`t_base_user`.`updated_at` AS `updated_at` from `lhrdb`.`t_base_user` where (`lhrdb`.`t_base_user`.`telephone` = '12345678901') |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t_base_user where telephone=cast(12345678901 as char);
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t_base_user | NULL | ALL | NULL | NULL | NULL | NULL | 521550 | 100.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `lhrdb`.`t_base_user`.`oid` AS `oid`,`lhrdb`.`t_base_user`.`name` AS `name`,`lhrdb`.`t_base_user`.`email` AS `email`,`lhrdb`.`t_base_user`.`age` AS `age`,`lhrdb`.`t_base_user`.`telephone` AS `telephone`,`lhrdb`.`t_base_user`.`status` AS `status`,`lhrdb`.`t_base_user`.`created_at` AS `created_at`,`lhrdb`.`t_base_user`.`updated_at` AS `updated_at` from `lhrdb`.`t_base_user` where (convert(`lhrdb`.`t_base_user`.`telephone` using utf8) = (cast(12345678901 as char charset utf8))) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from `lhrdb`.`t_base_user` where (convert(`lhrdb`.`t_base_user`.`telephone` using utf8) = cast(12345678901 as char charset utf8));
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t_base_user | NULL | ALL | NULL | NULL | NULL | NULL | 521550 | 100.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show full columns from lhrdb.t_base_user;
+------------+-------------+-------------------+------+-----+-------------------+----------------+---------------------------------+-------------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------+-------------+-------------------+------+-----+-------------------+----------------+---------------------------------+-------------------+
| oid | bigint(20) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| name | varchar(30) | latin1_swedish_ci | YES | MUL | NULL | | select,insert,update,references | name |
| email | varchar(30) | latin1_swedish_ci | YES | MUL | NULL | | select,insert,update,references | email |
| age | int(11) | NULL | YES | | NULL | | select,insert,update,references | age |
| telephone | varchar(30) | latin1_swedish_ci | YES | MUL | NULL | | select,insert,update,references | telephone |
| status | tinyint(4) | NULL | YES | | NULL | | select,insert,update,references | 0 無效 1 有效 |
| created_at | datetime | NULL | YES | | CURRENT_TIMESTAMP | | select,insert,update,references | 建立時間 |
| updated_at | datetime | NULL | YES | | CURRENT_TIMESTAMP | | select,insert,update,references | 修改時間 |
+------------+-------------+-------------------+------+-----+-------------------+----------------+---------------------------------+-------------------+
8 rows in set (0.00 sec)

mysql> show index from lhrdb.t_base_user;
+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_base_user | 0 | PRIMARY | 1 | oid | A | 521550 | NULL | NULL | | BTREE | | |
| t_base_user | 0 | idx_oid2 | 1 | oid | A | 521550 | NULL | NULL | | BTREE | | |
| t_base_user | 0 | idx_id_updated_at | 1 | oid | A | 521550 | NULL | NULL | | BTREE | | |
| t_base_user | 0 | idx_id_updated_at | 2 | updated_at | A | 521550 | NULL | NULL | YES | BTREE | | |
| t_base_user | 1 | idx_email | 1 | email | A | 521550 | NULL | NULL | YES | BTREE | | |
| t_base_user | 1 | idx_name | 1 | name | A | 521550 | NULL | NULL | YES | BTREE | | |
| t_base_user | 1 | idx_telephone | 1 | telephone | A | 521550 | NULL | NULL | YES | BTREE | | |
| t_base_user | 1 | idx_oid | 1 | oid | A | 521550 | NULL | NULL | | BTREE | | |
| t_base_user | 1 | idx_id_name | 1 | oid | A | 521550 | NULL | NULL | | BTREE | | |
| t_base_user | 1 | idx_id_name | 2 | name | A | 521550 | NULL | NULL | YES | BTREE | | |
| t_base_user | 1 | idx_id_created_at | 1 | oid | A | 521550 | NULL | NULL | | BTREE | | |
| t_base_user | 1 | idx_id_created_at | 2 | created_at | A | 521550 | NULL | NULL | YES | BTREE | | |
| t_base_user | 1 | idx_telephone_email | 1 | telephone | A | 521550 | NULL | NULL | YES | BTREE | | |
| t_base_user | 1 | idx_telephone_email | 2 | email | A | 521550 | NULL | NULL | YES | BTREE | | |
+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 rows in set (0.00 sec)

mysql> explain select * from t_base_user where telephone=cast(12345678901 as char charset latin1);
+----+-------------+-------------+------------+------+-----------------------------------+---------------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------------------------+---------------+---------+-------+--------+----------+-------+
| 1 | SIMPLE | t_base_user | NULL | ref | idx_telephone,idx_telephone_email | idx_telephone | 33 | const | 260775 | 100.00 | NULL |
+----+-------------+-------------+------------+------+-----------------------------------+---------------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `lhrdb`.`t_base_user`.`oid` AS `oid`,`lhrdb`.`t_base_user`.`name` AS `name`,`lhrdb`.`t_base_user`.`email` AS `email`,`lhrdb`.`t_base_user`.`age` AS `age`,`lhrdb`.`t_base_user`.`telephone` AS `telephone`,`lhrdb`.`t_base_user`.`status` AS `status`,`lhrdb`.`t_base_user`.`created_at` AS `created_at`,`lhrdb`.`t_base_user`.`updated_at` AS `updated_at` from `lhrdb`.`t_base_user` where (`lhrdb`.`t_base_user`.`telephone` = cast(12345678901 as char charset latin1)) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t_base_user where oid='1';
+----+-------------+-------------+------------+-------+--------------------------------------------------------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+--------------------------------------------------------------------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_base_user | NULL | const | PRIMARY,idx_oid2,idx_id_updated_at,idx_oid,idx_id_name,idx_id_created_at | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+-------+--------------------------------------------------------------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_base_user where oid=1;
+----+-------------+-------------+------------+-------+--------------------------------------------------------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+--------------------------------------------------------------------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_base_user | NULL | const | PRIMARY,idx_oid2,idx_id_updated_at,idx_oid,idx_id_name,idx_id_created_at | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+-------+--------------------------------------------------------------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

SYS@PROD1> set linesize 1000
SYS@PROD1> set autot on
SYS@PROD1> select * from t_base_user where telephone=12345678901;

OID NAME EMAIL TELEPHONE
---------- ------------------------------ ------------------------------ ------------------------------
111111 lhrbest@gmail.com 111 12345678901

Execution Plan
----------------------------------------------------------

Plan hash value: 3483759010

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_BASE_USER | 1 | 64 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_NUMBER("TELEPHONE")=12345678901)

Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
633 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SYS@PROD1> select * from t_base_user where telephone='12345678901';

OID NAME EMAIL TELEPHONE
---------- ------------------------------ ------------------------------ ------------------------------
111111 lhrbest@gmail.com 111 12345678901

Execution Plan
----------------------------------------------------------
Plan hash value: 1190906832

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_BASE_USER | 1 | 64 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TELEPHONE | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("TELEPHONE"='12345678901')

Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistics
----------------------------------------------------------
33 recursive calls
0 db block gets
11 consistent gets
2 physical reads
0 redo size
637 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SYS@PROD1>

MySQL隱式轉化整理

			
rollenholt 2016-05-06 16:04:42 瀏覽1031 評論0

雲資料庫RDS

摘要：前幾天在微博上看到一篇文章：價值百萬的 MySQL 的隱式型別轉換感覺寫的很不錯，再加上自己之前也對MySQL的隱式轉化這邊並不是很清楚，所以就順勢整理了一下。希望對大家有所幫助。當我們對不同型別的值進行比較的時候，為了使得這些數值「可比較」（也可以稱為型別的相容性)，MySQL會做一些隱式轉化（Implicit type conversion）。

					前幾天在微博上看到一篇文章：價值百萬的 MySQL 的隱式型別轉換感覺寫的很不錯，再加上自己之前也對MySQL的隱式轉化這邊並不是很清楚，所以就順勢整理了一下。希望對大家有所幫助。 
				
					當我們對不同型別的值進行比較的時候，為了使得這些數值「可比較」（也可以稱為型別的相容性)，MySQL會做一些隱式轉化（Implicit type conversion）。比如下面的例子： 
				
mysql> SELECT 1+'1';
        -> 2 mysql> SELECT CONCAT(2,' test');
        -> '2 test'
			
				很明顯，上面的SQL語句的執行過程中就出現了隱式轉化。並且從結果們可以判斷出，第一條SQL中，將字串的“1”轉換為數字1，而在第二條的SQL中，將數字2轉換為字串“2”。 
			
				MySQL也提供了CAST()函式。我們可以使用它明確的把數值轉換為字串。當使用CONCA()函式的時候，也可能會出現隱式轉化，因為它希望的引數為字串形式，但是如果我們傳遞的不是字串呢： 
			
mysql> SELECT 38.8, CAST(38.8 AS CHAR);
        -> 38.8, '38.8' mysql> SELECT 38.8, CONCAT(38.8);
        -> 38.8, '38.8'
				
 隱式轉化規則 
					
						官方文件中關於隱式轉化的規則是如下描述的： 
					
						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. 
							
							翻譯為中文就是： 
						
 兩個引數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做型別轉換 
							
 兩個引數都是字串，會按照字串來比較，不做型別轉換 
						
 兩個引數都是整數，按照整數來比較，不做型別轉換 
							
 十六進位制的值和非數字做比較時，會被當做二進位制串 
						
 有一個引數是 TIMESTAMP 或 DATETIME，並且另外一個引數是常量，常量會被轉換為 timestamp 
							
 有一個引數是 decimal 型別，如果另外一個引數是 decimal 或者整數，會將整數轉換為 decimal 後進行比較，如果另外一個引數是浮點數，則會把 decimal 轉換為浮點數進行比較 
						
 所有其他情況下，兩個引數都會被轉換為浮點數再進行比較 
							
 注意點 
									 安全問題：假如 password 型別為字串，查詢條件為 int 0 則會匹配上。 
										
mysql> select * from test;
+----+-------+-----------+ | id | name  | password |
+----+-------+-----------+ | 1 | test1 | password1 |
| 2 | test2 | password2 |
+----+-------+-----------+ 2 rows in set (0.00 sec)

mysql> select * from test where name = 'test1' and password = 0;
+----+-------+-----------+ | id | name  | password |
+----+-------+-----------+ | 1 | test1 | password1 |
+----+-------+-----------+ 1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+ | Level | Code | Message                                       |
+---------+------+-----------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'password1' |
+---------+------+-----------------------------------------------+ 1 row in set (0.00 sec)
											
												相信上面的例子，一些機靈的同學可以發現其實上面的例子也可以做sql注入。 
											
												假設網站的登入那塊做的比較挫，使用下面的方式： 
											
SELECT * FROM users WHERE username = '$_POST["username"]' AND password = '$_POST["password"]'
										
											如果username輸入的是a' OR 1='1，那麼password隨便輸入，這樣就生成了下面的查詢： 
										
SELECT * FROM users WHERE username = 'a' OR 1='1' AND password = 'anyvalue'
											
												就有可能登入系統。其實如果攻擊者看過了這篇文章，那麼就可以利用隱式轉化來進行登入了。如下： 
											
mysql> select * from test;
+----+-------+-----------+ | id | name  | password |
+----+-------+-----------+ | 1 | test1 | password1 |
| 2 | test2 | password2 |
| 3 | aaa   | aaaa      |
| 4 | 55aaa | 55aaaa    |
+----+-------+-----------+ 4 rows in set (0.00 sec)

mysql> select * from test where name = 'a' + '55';
+----+-------+----------+ | id | name  | password |
+----+-------+----------+ | 4 | 55aaa | 55aaaa   |
+----+-------+----------+ 1 row in set, 5 warnings (0.00 sec)
										
											之所以出現上述的原因是因為： 
										
mysql> select '55aaa' = 55;
+--------------+ | '55aaa' = 55 |
+--------------+ | 1 |
+--------------+ 1 row in set, 1 warning (0.00 sec)

mysql> select 'a' + '55';
+------------+ | 'a' + '55' |
+------------+ | 55 |
+------------+ 1 row in set, 1 warning (0.00 sec)
											
												下面透過一些例子來複習一下上面的轉換規則： 
											
mysql> select 1+1;
+-----+ | 1+1 |
+-----+ | 2 |
+-----+ 1 row in set (0.00 sec)

mysql> select 'aa' + 1;
+----------+ | 'aa' + 1 |
+----------+ | 1 |
+----------+ 1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+ | Level | Code | Message                                |
+---------+------+----------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' |
+---------+------+----------------------------------------+ 1 row in set (0.00 sec)
										
											把字串“aa”和1進行求和，得到1，因為“aa”和數字1的型別不同，MySQL官方文件告訴我們： 
										
											When an operator is used with operands of different types, type conversion occurs to make the operands compatible. 
										
												檢視warnings可以看到隱式轉化把字串轉為了double型別。但是因為字串是非數字型的，所以就會被轉換為0，因此最終計算的是0+1=1 
											
												上面的例子是型別不同，所以出現了隱式轉化，那麼如果我們使用相同型別的值進行運算呢？ 
											
mysql> select 'a' + 'b';
+-----------+ | 'a' + 'b' |
+-----------+ | 0 |
+-----------+ 1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+ | Level | Code | Message                               |
+---------+------+---------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'b' |
+---------+------+---------------------------------------+ 2 rows in set (0.00 sec)
												
													是不是有點鬱悶呢？ 
												
													之所以出現這種情況，是因為+為算術運算子arithmetic operator 這樣就可以解釋為什麼a和b都轉換為double了。因為轉換之後其實就是：0+0=0了。 
												
													在看一個例子： 
												
mysql> select 'a'+'b'='c';
+-------------+ | 'a'+'b'='c' |
+-------------+ | 1 |
+-------------+ 1 row in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+ | Level | Code | Message                               |
+---------+------+---------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'b' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'c' |
+---------+------+---------------------------------------+ 3 rows in set (0.00 sec)
											
												現在就看也很好的理解上面的例子了吧。a+b=c結果為1，1在MySQL中可以理解為TRUE，因為'a'+'b'的結果為0，c也會隱式轉化為0，因此比較其實是：0=0也就是true，也就是1. 
											
 第二個需要注意點就是防止多查詢或者刪除資料 
												
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)
													
														上面的例子本意是查詢id為5的那一條記錄，結果把id為6的那一條也查詢出來了。我想說明什麼情況呢？有時候我們的資料庫表中的一些列是varchar型別，但是儲存的值為‘1123’這種的純數字的字串值，一些同學寫sql的時候又不習慣加引號。這樣當進行select，update或者delete的時候就可能會多操作一些資料。所以應該加引號的地方別忘記了。 
													
 關於字串轉數字的一些說明 
														
 mysql> select 'a' = 0;
+---------+ | 'a' = 0 |
+---------+ | 1 |
+---------+ 1 row in set, 1 warning (0.00 sec)

mysql> select '1a' = 1;
+----------+ | '1a' = 1 |
+----------+ | 1 |
+----------+ 1 row in set, 1 warning (0.00 sec)

mysql> select '1a1b' = 1;
+------------+ | '1a1b' = 1 |
+------------+ | 1 |
+------------+ 1 row in set, 1 warning (0.00 sec)

mysql> select '1a2b3' = 1;
+-------------+ | '1a2b3' = 1 |
+-------------+ | 1 |
+-------------+ 1 row in set, 1 warning (0.00 sec)

mysql> select 'a1b2c3' = 0;
+--------------+ | 'a1b2c3' = 0 |
+--------------+ | 1 |
+--------------+ 1 row in set, 1 warning (0.00 sec)
															
																從上面的例子可以看出，當把字串轉為數字的時候，其實是從左邊開始處理的。 
															
 如果字串的第一個字元就是非數字的字元，那麼轉換為數字就是0 
																
 如果字串以數字開頭 
															
 如果字串中都是數字，那麼轉換為數字就是整個字串對應的數字 
																
 如果字串中存在非數字，那麼轉換為的數字就是開頭的那些數字對應的值 
															
																	如果你有其他更好的例子，或者被隱式轉化坑過的情況，歡迎分享。 
																
 參考資料 
																	 http://dev.mysql.com/doc/refman/5.7/en/cast-functions.html 
																		
 https://blog.eood.cn/mysql_params 
																	
 http://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

前言：MySQL的隱式轉換是什麼樣子的，什麼時候會進行隱式轉換，下面讓我們來掀開MySQL隱式轉換的面紗，下面我們透過一個例子來進行說明

隱式轉換的幾種情況：

1、當不同型別的欄位一起使用時，會發生隱式轉換

root@localhost:mysql.sock  16:18:23 [tom]>SELECT 1+'1';
+-------+| 1+'1' |
+-------+|     2 |
+-------+1 row in set (0.00 sec)

root@localhost:mysql.sock  16:18:26 [tom]>SELECT CONCAT(2,' test');
+-------------------+| CONCAT(2,' test') |
+-------------------+| 2 test            |
+-------------------+1 row in set (0.00 sec)

或者使用函式顯示或者隱式進行轉換

root@localhost:mysql.sock  16:29:39 [tom]>SELECT 38.8, CAST(38.8 AS CHAR);
+------+--------------------+
| 38.8 | CAST(38.8 AS CHAR) |
+------+--------------------+
| 38.8 | 38.8               |
+------+--------------------+1 row in set (0.00 sec)root@localhost:mysql.sock  16:30:27 [tom]>SELECT 38.8, CONCAT(38.8);
+------+--------------+
| 38.8 | CONCAT(38.8) |
+------+--------------+
| 38.8 | 38.8         |
+------+--------------+1 row in set (0.00 sec)

2、下面是一些會發生隱式轉換的規則

1、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.2、If both arguments in a comparison operation are strings, they are compared as strings.3、If both arguments are integers, they are compared as integers.4、Hexadecimal values are treated as binary strings if not compared to a number.5、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.
6、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.
7、In all other cases, the arguments are compared as floating-point (real) numbers.

3、下面是字串轉換成數字進行比較的

root@localhost:mysql.sock  17:23:38 [tom]>SELECT 1 > '6x';
+----------+| 1 > '6x' |
+----------+|        0 |
+----------+1 row in set, 1 warning (0.00 sec)

root@localhost:mysql.sock  17:23:41 [tom]>SELECT 7 > '6x';
+----------+| 7 > '6x' |
+----------+|        1 |
+----------+1 row in set, 1 warning (0.01 sec)

root@localhost:mysql.sock  17:23:48 [tom]>SELECT 0 > 'x6';
+----------+| 0 > 'x6' |
+----------+|        0 |
+----------+1 row in set, 1 warning (0.00 sec)

root@localhost:mysql.sock  17:23:55 [tom]>SELECT 0 = 'x6';
+----------+| 0 = 'x6' |
+----------+|        1 |
+----------+1 row in set, 1 warning (0.00 sec)

4、如果在sql中對一個字串和數字比較，mysql不會使用索引

root@localhost:mysql.sock  17:24:03 [tom]>explain select * from test where name = 1;;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | name          | NULL | NULL    | NULL |    9 |    11.11 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 3 warnings (0.00 sec)ERROR: No query specified

root@localhost:mysql.sock  17:25:42 [tom]>show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(4) NOT NULL DEFAULT 'tom',
  KEY `name` (`name`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

The reason for this is that there are many different strings that may convert to the value1, such as‘1’,‘ 1’, or ‘1a’

5、浮點數的比較是近似值，可能導致結果不準確，可能會有取捨

root@localhost:mysql.sock  17:30:19 [tom]>SELECT '18015376320243459'+0.0;
+-------------------------+| '18015376320243459'+0.0 |
+-------------------------+|    1.801537632024346e16 |
+-------------------------+1 row in set (0.00 sec)

浮點數與整數之間的轉換，或者符號之間的轉換最好使用函式CAST()，避免隱式轉換

總結：

1、sql中的where條件禁止進行不同欄位型別的比較

2、字串型別和數字比較會轉換成0然後進行比較

3、字串轉換成數字的時候是從最左邊開始的（不是數字就是0，是數字就匹配最左數字，這就導致可能會多查詢或者刪除、更新資料，這個結果就比較悲催了）

小心MySQL的隱式型別轉換陷阱

																					 Posted on 2016-05-05 |  In |  |  Visitors 497 
																				

																					1. 隱式型別轉換例項 
																				今天生產庫上突然出現MySQL執行緒數告警，IOPS很高，例項會話裡面出現許多類似下面的sql：(修改了相關欄位和值)
																					
																					SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 and f_col2_id=1244378 and 
																				
																					f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233) 
																				
 mysql>explain extended SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1  use index(idx_corpid_qq1id) WHERE f_col1_id=1226391 and f_col2_id=1244378 and f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233); 
 用 explain 看了下掃描行數和索引選擇情況：

																					mysql>explain SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 
																				
																					and f_col2_id=1244378 and f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233); 
																				
																					+------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+ 
																				
																					| id   | select_type   | table   | type   | possible_keys                  | key           | key_len    | ref    | rows   | Extra                              | 
																				
																					+------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+ 
																				
																					| 1    | SIMPLE        | t_tb1   | ref    | uid_type_frid,idx_corpid_qq1id | uid_type_frid | 8          | const  | 1386   | Using index condition; Using where | 
																				
																					+------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+ 
																				
																					共返回 1 行記錄,花費 11.52 ms. 
																				
 t_tb1 表上有個索引uid_type_frid(f_col2_id,f_type)、idx_corp_id_qq1id(f_col1_id,f_qq1_id)，而且如果選擇後者時，f_qq1_id的過濾效果應該很佳，但卻選擇了前者。當使用 hint use index(idx_corp_id_qq1id)時：
																					
																					+------+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+ 
																				
																					| id   | select_type   | table  | type   | possible_keys       | key              | key_len    | ref      | rows        | Extra                              | 
																				
																					+------+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+ 
																				
																					| 1 | SIMPLE        | t_tb1  | ref    | idx_corpid_qq1id    | idx_corpid_qq1id | 8 | const    | 2375752 | Using index condition; Using where | 
																				
																					+---- -+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+ 
																				
																					共返回 1 行記錄,花費 17.48 ms. 
																				
																					mysql>show warnings; 
																				
																					+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+ 
																				
																					| Level           | Code           | Message                                                                                                               | 
																				
																					+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+ 
																				
																					| Warning         | 1739 | Cannot use range access on index 'idx_corpid_qq1id' due to type or collation conversion on field 'f_qq1_id'           | 
																				
																					| Note            | 1003 | /* select#1 */ select `d_dbname`.`t_tb1`.`f_col3_id` AS `f_col3_id`,`d_dbname`.`t_tb1`.`f_qq1_id` AS `f_qq1_id` from `d_dbname`.`t_tb1` USE INDEX (`idx_corpid_qq1id`) where | 
																				
																					|                 |                |  ((`d_dbname`.`t_tb1`.`f_col2_id` = 1244378) and (`d_dbname`.`t_tb1`.`f_col1_id` = 1226391) and (`d_dbname`.`t_tb1`.`f_qq1_id` in | 
																				
																					|                 |                | (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233)))                                        | 
																				
																					+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+ 
																				
																					共返回 2 行記錄,花費 10.81 ms. 
																				
 rows列達到200w行，但問題也發現了：select_type應該是 range 才對，key_len看出來只用到了idx_corpid_qq1id索引的第一列。上面explain使用了 extended，所以show warnings;可以很明確的看到 f_qq1_id 出現了隱式型別轉換：f_qq1_id是varchar，而後面的比較值是整型。
																					
解決該問題就是避免出現隱式型別轉換(implicit type conversion)帶來的不可控：把f_qq1_id in的內容寫成字串：
																					
																					mysql>explain SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 and f_col2_id=1244378 and 
																				
																					f_qq1_id in ('12345','23456','34567','45678','56789','67890','78901','89012','90123','901231'); 
																				
																					+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+ 
																				
																					| id    | select_type   | table  | type    | possible_keys                  | key              | key_len     | ref     | rows    | Extra                              | 
																				
																					+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+ 
																				
																					| 1     | SIMPLE        | t_tb1  | range   | uid_type_frid,idx_corpid_qq1id | idx_corpid_qq1id | 70          |         | 40      | Using index condition; Using where | 
																				
																					+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+ 
																				
																					共返回 1 行記錄,花費 12.41 ms. 
																				
 類似的還出現過一例：掃描行數從1386減少為40。
																					
																					SELECT count(0) FROM d_dbname.t_tb2 where f_col1_id= '1931231' AND f_phone in(098890); 
																				
																					| Warning | 1292 | Truncated incorrect DOUBLE value: '1512-98464356' 
																				
 借這個機會，系統的來看一下mysql中的隱式型別轉換。最佳化後直接從掃描rows 100w行降為1。
																					
																					2. mysql隱式轉換規則 
																				
																					2.1 規則 
																				下面來分析一下隱式轉換的規則：
																					
a. 兩個引數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做型別轉換
 b. 兩個引數都是字串，會按照字串來比較，不做型別轉換
 c. 兩個引數都是整數，按照整數來比較，不做型別轉換
 d. 十六進位制的值和非數字做比較時，會被當做二進位制串
 e. 有一個引數是 TIMESTAMP 或 DATETIME，並且另外一個引數是常量，常量會被轉換為 timestamp
 f. 有一個引數是 decimal 型別，如果另外一個引數是 decimal 或者整數，會將整數轉換為 decimal 後進行比較，如果另外一個引數是浮點數，則會把 decimal 轉換為浮點數進行比較
 g. 所有其他情況下，兩個引數都會被轉換為浮點數再進行比較
																						
																					mysql> select 11 + '11', 11 + 'aa', 'a1' + 'bb', 11 + '0.01a';  
																				
																					+-----------+-----------+-------------+--------------+ 
																				
																					| 11 + '11' | 11 + 'aa' | 'a1' + 'bb' | 11 + '0.01a' | 
																				
																					+-----------+-----------+-------------+--------------+ 
																				
																					|        22 |        11 |           0 |        11.01 | 
																				
																					+-----------+-----------+-------------+--------------+ 
																				
																					1 row in set, 4 warnings (0.00 sec) 
																				
																					mysql> show warnings; 
																				
																					+---------+------+-------------------------------------------+ 
																				
																					| Level   | Code | Message                                   | 
																				
																					+---------+------+-------------------------------------------+ 
																				
																					| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' | 
																				
																					| Warning | 1292 | Truncated incorrect DOUBLE value: 'a1' | 
																				
																					| Warning | 1292 | Truncated incorrect DOUBLE value: 'bb' | 
																				
																					| Warning | 1292 | Truncated incorrect DOUBLE value: '0.01a' | 
																				
																					+---------+------+-------------------------------------------+ 
																				
																					4 rows in set (0.00 sec) 
																				
																					mysql> select '11a' = 11, '11.0' = 11, '11.0' = '11', NULL = 1; 
																				
																					+------------+-------------+---------------+----------+ 
																				
																					| '11a' = 11 | '11.0' = 11 | '11.0' = '11' | NULL = 1 | 
																				
																					+------------+-------------+---------------+----------+ 
																				
																					|          1 |           1 |             0 |     NULL | 
																				
																					+------------+-------------+---------------+----------+ 
																				
																					1 row in set, 1 warning (0.01 sec) 
																				
 0.01a轉成double型也是被截斷成0.01，所以11 + '0.01a' = 11.01。上面可以看出11 + 'aa'，由於運算子兩邊的型別不一樣且符合第g條，aa要被轉換成浮點型小數，然而轉換失敗（字母被截斷），可以認為轉成了 0，整數11被轉成浮點型還是它自己，所以11 + 'aa' = 11。
																					
等式比較也說明了這一點，'11a'和'11.0'轉換後都等於 11，這也正是文章開頭例項為什麼沒走索引的原因： varchar型的f_qq1_id，轉換成浮點型比較時，等於 12345 的情況有無數種如12345a、12345.b等待，MySQL最佳化器無法確定索引是否更有效，所以選擇了其它方案。
																					
但並不是只要出現隱式型別轉換，就會引起上面類似的效能問題，最終是要看轉換後能否有效選擇索引。像f_id = '654321'、f_mtime between '2016-05-01 00:00:00' and '2016-05-04 23:59:59'就不會影響索引選擇，因為前者f_id是整型，即使與後面的字串型數字轉換成double比較，依然能根據double確定f_id的值，索引依然有效。後者是因為符合第e條，只是右邊的常量做了轉換。
																					
開發人員可能都只要存在這麼一個隱式型別轉換的坑，但卻又經常不注意，所以乾脆無需記住那麼多規則，該什麼型別就與什麼型別比較。
																					
																					2.2 隱式型別轉換的安全問題 
																				implicit type conversion 不僅可能引起效能問題，還有可能產生安全問題。
																					
																					mysql> desc t_account; 
																				
																					+-----------+-------------+------+-----+---------+----------------+ 
																				
																					| Field     | Type        | Null | Key | Default | Extra          | 
																				
																					+-----------+-------------+------+-----+---------+----------------+ 
																				
																					| fid       | int(11)     | NO   | PRI | NULL    | auto_increment | 
																				
																					| fname     | varchar(20) | YES  |     | NULL    |                | 
																				
																					| fpassword | varchar(50) | YES  |     | NULL    |                | 
																				
																					+-----------+-------------+------+-----+---------+----------------+ 
																				
																					mysql> select * from t_account; 
																				
																					+-----+-----------+-------------+ 
																				
																					| fid | fname     | fpassword   | 
																				
																					+-----+-----------+-------------+ 
																				
																					|   1 | xiaoming  | p_xiaoming  | 
																				
																					|   2 | xiaoming1 | p_xiaoming1 | 
																				
																					+-----+-----------+-------------+ 
																				
																					假如應用前端沒有WAF防護，那麼下面的sql很容易注入： 
																				
																					mysql> select * from t_account where fname='A' ; 
																				
																					fname傳入  A' OR 1='1  
																				
																					mysql> select * from t_account where fname='A' OR 1='1'; 
																				
攻擊者更聰明一點： fname傳入 A'+'B ，fpassword傳入 ccc'+0 ：
																					
																					mysql> select * from t_account where fname='A'+'B' and fpassword='ccc'+0; 
																				
																					+-----+-----------+-------------+ 
																				
																					| fid | fname     | fpassword   | 
																				
																					+-----+-----------+-------------+ 
																				
																					|   1 | xiaoming  | p_xiaoming  | 
																				
																					|   2 | xiaoming1 | p_xiaoming1 | 
																				
																					+-----+-----------+-------------+ 
																				
																					2 rows in set, 7 warnings (0.00 sec) 
																				
 參考
																					
																						WHRER條件裡的資料型別必須和欄位資料型別一致 
																					
原文連結地址：

About Me

.............................................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：

.............................................................................................................................................

● QQ群號：230161599（滿）、618766405

● 微信群：可加我微信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友（646634621），註明新增緣由

● 於 2017-12-01 09:00 ~ 2017-12-31 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店：

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

小麥苗的微信公眾號小麥苗的DBA寶典QQ群2 《DBA筆試面寶典》讀者群小麥苗的微店

.............................................................................................................................................