也议MySQL中隐式转换 - 数据库编程

TOP

也议MySQL中隐式转换(一)

2015-07-24 10:42:53 来源: 作者: 【大中小】浏览:3次

1. 环境说明

blog地址：http://blog.csdn.net/hw_libo/article/details/39252427

RHEL 6.4 x86_64 + MySQL 5.6.19

测试表：

MySQL [test]> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `EMPNO` int(11) NOT NULL,
  `ENAME` varchar(15) NOT NULL,
  `JOB` varchar(15) NOT NULL,
  `MGR` int(11) DEFAULT '0',
  `HIREDATE` timestamp NULL DEFAULT NULL,
  `SAL` int(20) DEFAULT '0',
  `COMM` int(11) DEFAULT '0',
  `DEPTNO` int(11) NOT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY `idx_deptno` (`DEPTNO`),
  KEY `idx_sal` (`SAL`),
  KEY `idx_comm` (`COMM`),
  KEY `idx_ename` (`ENAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MySQL [test]> select * from emp;
+-------+--------+-----------+------+---------------------+------+------+--------+
| EMPNO | ENAME ?| JOB ? ? ? | MGR ?| HIREDATE ? ? ? ? ? ?| SAL ?| COMM | DEPTNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| ?7369 | SMITH ?| CLERK ? ? | 7902 | 1980-12-17 00:00:00 | ?800 | NULL | ? ? 20 |
| ?7499 | ALLEN ?| SALESMAN ?| 7698 | 1981-02-20 00:00:00 | 1600 | ?300 | ? ? 30 |
| ?7521 | WARD ? | SALESMAN ?| 7698 | 1981-02-22 00:00:00 | 1250 | ?500 | ? ? 30 |
| ?7566 | JONES ?| MANAGER ? | 7839 | 1981-04-02 00:00:00 | 2975 | ? ?0 | ? ? 20 |
| ?7654 | MARTIN | SALESMAN ?| 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | ? ? 30 |
| ?7698 | BLAKE ?| MANAGER ? | 7839 | 1981-05-01 00:00:00 | 2850 | ? ?0 | ? ? 30 |
| ?7782 | CLARK ?| MANAGER ? | 7839 | 1981-06-09 00:00:00 | 2450 | ? ?0 | ? ? 10 |
| ?7788 | SCOTT ?| ANALYST ? | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | ? ? 20 |
| ?7839 | KING ? | PRESIDENT | ? ?0 | 1981-11-17 00:00:00 | 5000 | ? ?0 | ? ? 10 |
| ?7844 | TURNER | SALESMAN ?| 7698 | 1981-09-08 00:00:00 | 1500 | ? ?0 | ? ? 30 |
| ?7876 | ADAMS ?| CLERK ? ? | 7788 | 1987-05-23 00:00:00 | 1100 | ? ?0 | ? ? 20 |
| ?7900 | JAMES ?| CLERK ? ? | 7698 | 1981-12-03 00:00:00 | ?950 | ? ?0 | ? ? 30 |
| ?7902 | FORD ? | ANALYST ? | 7566 | 1981-12-03 00:00:00 | 3000 | ? ?0 | ? ? 20 |
| ?7934 | MILLER | CLERK ? ? | 7782 | 1982-01-23 00:00:00 | 1300 | ? ?0 | ? ? 10 |
| ?7936 | 23456 ?| BOSCO-DBA | 7788 | 2014-09-13 16:13:56 | 2450 | ?800 | ? ? 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
15 rows in set (0.00 sec)

2. 数值类型（int）

首先提个问题，如上测试表emp中empno是主键，类型为int，那么：

select * from emp where empno='7788';

会产生隐式转换吗？

下面实验证明：

MySQL [test]> select * from emp where empno=7788;
+-------+-------+---------+------+---------------------+------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+-------+---------+------+---------------------+------+------+--------+
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |
+-------+-------+---------+------+---------------------+------+------+--------+
1 row in set (0.00 sec)

MySQL [test]> explain select * from emp where empno=7788;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | emp   | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

MySQL [test]> select * from emp where empno='7788';
+--

首页上一页 1 2 3 下一页尾页 1/3/3
【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】
分享到:
上一篇：mysql启动参数(/etc/my.cnf)详解..	下一篇：mysql多主单向-->环形同步

帐　　号:

密码: (新用户注册)

验证码:

表　　情:

内　　容: