mysql select 1 from_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 |

+----------+------------+------------------------------------+