首页 > 代码库 > mysql select 1
mysql select 1
看数据库连接池源码，发现连接池的参数validationQuery(SQL查询,用来验证从连接池取出的连接)设置的值为"SELECT 1"，之前很少用这种写法，于是
google一下，总结如下:
以seven_user表为例，如下是建表语句：
CREATE TABLE `smily_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`version` int(4) NOT NULL DEFAULT ‘0‘,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
select 1；
select 1 from smily_test；
select id from smily_test；
select version from smily_test；
select * from smily_test；
以上sql语句执行的结果如下：
mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from smily_test;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
+---+
4 rows in set (0.00 sec)
mysql> select id from smily_test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
mysql> select version from smily_test;
+---------+
| version |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
+---------+
4 rows in set (0.00 sec)
mysql> select * from smily_test;
+----+---------+
| id | version |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+---------+
4 rows in set (0.00 sec)
用show profiles查看这些sql语句的执行效率；
mysql> show profiles;+----------+------------+--------------------------------+| Query_ID | Duration | Query |+----------+------------+--------------------------------+| 1 | 0.00017700 | select 1 || 2 | 0.00013400 | select 1 from smily_test || 3 | 0.00011000 | select id from smily_test || 4 | 0.00010500 | select version from smily_test || 5 | 0.00011700 | select * from smily_test |+----------+------------+--------------------------------+5 rows in set (0.00 sec)
作用上来说是没有差别的，都是查看是否有记录，一般是作条件用的.
看得出执行效率为： select 1 > select 1 from table > select anycolum from table > select * from table
再来看看 count(*) 和 count(1)的效率。
+----------+------------+------------------------------------+| Query_ID | Duration | Query |+----------+------------+------------------------------------+| 7 | 0.00037200 | select count(1) from smily_test || 8 | 0.00034500 | select count(*) from smily_test |+----------+------------+------------------------------------+
mysql select 1