mysql中的单引号/小数点/字符转换为数字/警告信息

我们准备玩点有趣的：

select 一个数字:

mysql> select 1 from mysql.user;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
+---+
3 rows in set (0.00 sec)

mysql>

select 一个字符串:

mysql> select 'perl6' from mysql.user;
+-------+
| perl6 |
+-------+
| perl6 |
| perl6 |
| perl6 |
+-------+
3 rows in set (0.00 sec)

mysql>

这个字符串单/双引号是一样的， 我们可以去掉空格:

mysql> select'perl6'from mysql.user;
+-------+
| perl6 |
+-------+
| perl6 |
| perl6 |
| perl6 |
+-------+
3 rows in set (0.00 sec)

mysql>

可以看到正常执行。

那数字行不行呢？

mysql> select888from mysql.user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'select888from mysql.user' at line 1
mysql>

不行， 那我们再select一个浮点型看看：

mysql> select 0.8 from mysql.user;
+-----+
| 0.8 |
+-----+
| 0.8 |
| 0.8 |
| 0.8 |
+-----+
3 rows in set (0.00 sec)

mysql>

小数点前后如果是0， 可以不写：

mysql> select 1. from mysql.user;
+----+
| 1. |
+----+
|  1 |
|  1 |
|  1 |
+----+
3 rows in set (0.00 sec)

mysql> select .6 from mysql.user;
+-----+
| .6  |
+-----+
| 0.6 |
| 0.6 |
| 0.6 |
+-----+
3 rows in set (0.00 sec)

mysql>

如果是浮点型的话， 我们就可以让这个数字跟后面的关链字连在一起了：

mysql> select.6 from mysql.user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'select.6 from mysql.user' at line 1
mysql> select .6from mysql.user;
+-----+
| .6  |
+-----+
| 0.6 |
| 0.6 |
| 0.6 |
+-----+
3 rows in set (0.00 sec)

mysql>

.0就相当于0.0了， 可以这样写：

mysql> select 0.0 from mysql.user;
+-----+
| 0.0 |
+-----+
| 0.0 |
| 0.0 |
| 0.0 |
+-----+
3 rows in set (0.00 sec)

mysql> select .0from mysql.user;
+-----+
| .0  |
+-----+
| 0.0 |
| 0.0 |
| 0.0 |
+-----+
3 rows in set (0.00 sec)

mysql>

很好玩吧。

我们再看看别名：

mysql> select 'perl6' as P from mysql.user;
+-------+
| P     |
+-------+
| perl6 |
| perl6 |
| perl6 |
+-------+
3 rows in set (0.00 sec)

mysql>

as可省略：

mysql> select 'perl6'  P from mysql.user;
+-------+
| P     |
+-------+
| perl6 |
| perl6 |
| perl6 |
+-------+
3 rows in set (0.00 sec)

mysql>

省略后可写在一起:

mysql> select'perl6'P from mysql.user;
+-------+
| P     |
+-------+
| perl6 |
| perl6 |
| perl6 |
+-------+
3 rows in set (0.01 sec)

mysql>

那数字能不能起别名呢？也可以：

mysql> select 1 as 'perl6' from mysql.user;
+-------+
| perl6 |
+-------+
|     1 |
|     1 |
|     1 |
+-------+
3 rows in set (0.00 sec)

mysql>

结合前面的浮点型与省略as, 可以这样写:

mysql> select .0'perl6'from mysql.user;
+-------+
| perl6 |
+-------+
|   0.0 |
|   0.0 |
|   0.0 |
+-------+
3 rows in set (0.00 sec)

mysql>

我们用union select 再看看：

mysql> select .0from mysql.user union select .01;
+------+
| .0   |
+------+
| 0.00 |
| 0.01 |
+------+
2 rows in set (0.00 sec)

mysql>

加个条件:

mysql> select .0from mysql.user where user='root' union select .01;
+------+
| .0   |
+------+
| 0.00 |
| 0.01 |
+------+
2 rows in set (0.00 sec)

mysql>

再改改：

mysql> select .0from mysql.user where 1. union select 'perl6';
+-------+
| .0    |
+-------+
| 0.0   |
| perl6 |
+-------+
2 rows in set (0.00 sec)

mysql>

小数点数字可以跟后面关链字连起来的， 上面说了， 所以， 我们还可以这样:

mysql> select .0from mysql.user where .1union select'perl6';
+-------+
| .0    |
+-------+
| perl6 |
+-------+
1 row in set (0.00 sec)

mysql>

0.开头的小数点会转化为整数， 都转为0。

除了where, 还有一个类似的， 叫做having:

mysql> select .0from mysql.user having .0union select'perl6';
+-------+
| .0    |
+-------+
| perl6 |
+-------+
1 row in set (0.00 sec)

mysql>

where 跟having可以一起用:

mysql> select .0from mysql.user where .0 having 1 union select'perl6';
+-------+
| .0    |
+-------+
| perl6 |
+-------+
1 row in set (0.00 sec)

mysql> select .0from mysql.user where 1.0 having 1 union select'perl6';
+-------+
| .0    |
+-------+
| 0.0   |
| perl6 |
+-------+
2 rows in set (0.00 sec)

mysql>

where跟having一起时， 逻辑是从左到右， 先执行前面的where, 再执行后面的having。

但是having只能跟在where后面。

而且你也不能这么写: (select user from mysql.user where user='root' where user='root')

除了where/having类似外， 还有类似的：

union select 
union distinct select 
union all select

最后我们来看一下字符串转换为数字：

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

mysql>

'a'转换为0， 相加结果是1。

提示有警告， 可以用如下命令查看信息:

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

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql>

字符串会被转换为数字， 如果能转换， 就转换为相应的数字， 不能转换就转换为0

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

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

mysql> select '123'+1;
+---------+
| '123'+1 |
+---------+
|     124 |
+---------+
1 row in set (0.00 sec)

mysql>

我们可以连着一起写：

mysql> select'per16'+.1'test';
+------+
| test |
+------+
|  0.1 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql>

最后再来看点好玩的：

mysql> select host from mysql.user;
+-----------+
| host      |
+-----------+
| 127.0.0.1 |
| ::1       |
| localhost |
+-----------+
3 rows in set (0.00 sec)

mysql> select host from mysql.user where password='a'+'a';
+-----------+
| host      |
+-----------+
| localhost |
| 127.0.0.1 |
| ::1       |
+-----------+
3 rows in set, 5 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a'                                         |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a'                                         |
| Warning | 1292 | Truncated incorrect DOUBLE value: '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' |
+---------+------+-------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

password='a'+'a', 字符串'a'转换成数字（因为有个 + 号）， 会发生警告信息, 信息中包含用户的密码。

password='a'+'a', 会被转化为整数类型的表达式， 像 2=1+1

看下面的例子：

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select user()+'';
+-----------+
| user()+'' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

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

mysql> select user()=''+'1';
+---------------+
| user()=''+'1' |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql>

 本身user()是字符串的， 遇到了左边的 两个字符串相加， 所以右边的user()也将会自动转换为数值类型。