mysql的几种隐式转化

1. 表定义是字符型，传入的是Int

2. 字符集不一致。表定义的字段是gbk，传入的是utf8；这种在存储过程中出现得比较多。

数据库的字符集utf8

mysql> show create database jstmonitor;
+------------+---------------------------------------------------------------------+
| Database | Create Database |
+------------+---------------------------------------------------------------------+
| jstmonitor | CREATE DATABASE `jstmonitor` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

 

表的字符集gbk

mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`col1` varchar(10) NOT NULL,
`col2` bigint(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

 

创建了一个存储过程：

mysql> show create procedure proc1\G
*************************** 1. row ***************************
Procedure: proc1
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `proc1`(IN isn CHAR(20))
BEGIN
START TRANSACTION;
SELECT col2 FROM t1 WHERE col1= isn FOR UPDATE;
COMMIT;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)

 

调用存储过程：call proc1('tr');

show processlist看到的结果：

| 878794 | root            | 127.0.0.1:43746     | jstmonitor | Query       |      10 | Waiting for table level lock                                                | SELECT col2 FROM t1 WHERE col1=  NAME_CONST('isn',_utf8'tr' COLLATE 'utf8_general_ci') FOR UPDATE |         0 |             0 |         1 |

 

存储过程的输入列需要显式的定义输入字符集，否则其输入列的字符集将继承自库的字符集。

如果库的字符集和字段的字符集不一致，那么就发生隐式转换了。

 

3. 多表关联时，关联字段的字符集不一致；