mysql以下字符不同意义

最新推荐文章于 2023-09-25 15:22:31 发布

toddzk

最新推荐文章于 2023-09-25 15:22:31 发布

阅读量441

点赞数

分类专栏： Mysql基础文章标签： Mysql NULL

本文链接：https://blog.youkuaiyun.com/zkkdcs1/article/details/84634890

版权

Mysql基础专栏收录该内容

1 篇文章

订阅专栏

mysql中NULL,null,Null,’’,‘Null’,<>,@的字符不同意义

结论：
1、mysql命令行中@代表用户变量（可以用来存储值并供后续该mysql连接使用，连接断开变量释放）。
2、!=,<>两者都是不等于的意思，查阅网上资料说是!=是以前sql标准，<>是现在sql标准，推荐使用<>。
3、NULL,null,Null 这3个代表1个意思代表变量或字段无值。
4、where 类似于if条件根据值条件及真伪来执行语句。

测试证明：
1、mysql命令行中@代表用户变量（可以用来存储值并供后续该mysql连接使用，连接断开变量释放）。
mysql> set @set_dns=‘114.114.114.114’;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from epsc.config_dns_set where set_dns=@set_dns;
+----+---------+-----------------+--------+---------------------+
| id | casenum | set_dns         | switch | edit_time           |
+----+---------+-----------------+--------+---------------------+
|  1 |     100 | 114.114.114.114 |      0 | 2018-01-01 00:00:00 | 
|  2 |     100 | 114.114.114.114 |      1 | 2018-02-01 00:00:00 | 
|  3 |     100 | 114.114.114.114 |      0 | 0000-00-00 00:00:00 | 
+----+---------+-----------------+--------+---------------------+
3 rows in set (0.00 sec)

半小时后变量仍然可用
mysql> select * from epsc.config_dns_set where set_dns<>@set_dns;
+----+---------+---------+--------+---------------------+
| id | casenum | set_dns | switch | edit_time           |
+----+---------+---------+--------+---------------------+
|  4 |     100 |         |      0 | 0000-00-00 00:00:00 | 
|  5 |       0 |         |      0 | 0000-00-00 00:00:00 | 
|  6 |       0 |         |      0 | 0000-00-00 00:00:00 | 
|  7 |       0 | null    |      0 | 0000-00-00 00:00:00 | 
+----+---------+---------+--------+---------------------+
4 rows in set (0.00 sec)

断开该mysql连接，再sql连入，可以看到变量已不存在
[root@E21V19000 ~]# mysql -uroot -p
mysql> select * from epsc.config_dns_set where set_dns=@set_dns;
Empty set (0.00 sec)

2、!=,<>两者都是不等于的意思，查阅网上资料说是!=是以前sql标准，<>是现在sql标准，推荐使用<>。
mysql> select * from epsc.config_dns_set where set_dns<>@set_dns;
±—±--------±--------±-------±--------------------+
| id | casenum | set_dns | switch | edit_time |
±—±--------±--------±-------±--------------------+
| 4 | 100 | | 0 | 0000-00-00 00:00:00 |
| 5 | 0 | | 0 | 0000-00-00 00:00:00 |
| 6 | 0 | | 0 | 0000-00-00 00:00:00 |
| 7 | 0 | null | 0 | 0000-00-00 00:00:00 |
±—±--------±--------±-------±--------------------+
4 rows in set (0.00 sec)

mysql> select * from epsc.config_dns_set where set_dns!=@set_dns;
+----+---------+---------+--------+---------------------+
| id | casenum | set_dns | switch | edit_time           |
+----+---------+---------+--------+---------------------+
|  4 |     100 |         |      0 | 0000-00-00 00:00:00 | 
|  5 |       0 |         |      0 | 0000-00-00 00:00:00 | 
|  6 |       0 |         |      0 | 0000-00-00 00:00:00 | 
|  7 |       0 | null    |      0 | 0000-00-00 00:00:00 | 
+----+---------+---------+--------+---------------------+
4 rows in set (0.00 sec)

3、NULL,null,Null 这3个代表1个意思代表变量或字段值不存在;’'代表值为空，'Null’代表值为Null，仔细体会其不同。
下面3条语句结果都一样，证明3种写法都代表无值。
mysql> select * from epsc.config_dns_set where set_dns!=@set_dns and @zzz is NULL;
mysql> select * from epsc.config_dns_set where set_dns!=@set_dns and @zzz is Null;
mysql> select * from epsc.config_dns_set where set_dns!=@set_dns and @zzz is null;
±—±--------±--------±-------±--------------------+
| id | casenum | set_dns | switch | edit_time |
±—±--------±--------±-------±--------------------+
| 4 | 100 | | 0 | 0000-00-00 00:00:00 |
| 5 | 0 | | 0 | 0000-00-00 00:00:00 |
| 6 | 0 | | 0 | 0000-00-00 00:00:00 |
| 7 | 0 | null | 0 | 0000-00-00 00:00:00 |
±—±--------±--------±-------±--------------------+
4 rows in set (0.00 sec)

下面''代表有值且值为空，'Null'代表有值且值为Null。
mysql> select * from epsc.config_dns_set where set_dns='';
+----+---------+---------+--------+---------------------+
| id | casenum | set_dns | switch | edit_time           |
+----+---------+---------+--------+---------------------+
|  4 |     100 |         |      0 | 0000-00-00 00:00:00 | 
|  5 |       0 |         |      0 | 0000-00-00 00:00:00 | 
|  6 |       0 |         |      0 | 0000-00-00 00:00:00 | 
+----+---------+---------+--------+---------------------+
3 rows in set (0.03 sec)

mysql> select * from epsc.config_dns_set where set_dns='null';
+----+---------+---------+--------+---------------------+
| id | casenum | set_dns | switch | edit_time           |
+----+---------+---------+--------+---------------------+
|  7 |       0 | null    |      0 | 0000-00-00 00:00:00 | 
+----+---------+---------+--------+---------------------+
1 row in set (0.00 sec)

4、where 类似于if条件根据值条件及真伪来执行语句。

显示满足set_dns值为'114.114.114.114','Null',''且switch为0,1的条件的内容
select * from config_dns_set where set_dns in('114.114.114.114','Null','') and switch in (0,1); 
+----+---------+-----------------+--------+---------------------+
| id | casenum | set_dns         | switch | edit_time           |
+----+---------+-----------------+--------+---------------------+
|  1 |     100 | 114.114.114.114 |      0 | 2018-01-01 00:00:00 | 
|  2 |     100 | 114.114.114.114 |      1 | 2018-02-01 00:00:00 | 
|  3 |     100 | 114.114.114.114 |      0 | 0000-00-00 00:00:00 | 
|  4 |     100 |                 |      0 | 0000-00-00 00:00:00 | 
|  5 |       0 |                 |      0 | 0000-00-00 00:00:00 | 
|  6 |       0 |                 |      0 | 0000-00-00 00:00:00 | 
|  7 |       0 | null            |      0 | 0000-00-00 00:00:00 | 
+----+---------+-----------------+--------+---------------------+
7 rows in set (0.00 sec)

mysql> select @set_dns;
+-----------------+
| @set_dns        |
+-----------------+
| 114.114.114.114 | 
+-----------------+
1 row in set (0.00 sec)

mysql> select @name;
+-------+
| @name |
+-------+
| NULL  | 
+-------+
1 row in set (0.00 sec)
mysql> select * from config_dns_set where (set_dns = @set_dns or @set_dns is null) and (switch = @name or @name is null) ;
+----+---------+-----------------+--------+---------------------+
| id | casenum | set_dns         | switch | edit_time           |
+----+---------+-----------------+--------+---------------------+
|  1 |     100 | 114.114.114.114 |      0 | 2018-01-01 00:00:00 | 
|  2 |     100 | 114.114.114.114 |      1 | 2018-02-01 00:00:00 | 
|  3 |     100 | 114.114.114.114 |      0 | 0000-00-00 00:00:00 | 
+----+---------+-----------------+--------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from config_dns_set where (set_dns = @set_dns or @set_dns is null) and (switch = @name or @name=NULL);
Empty set (0.00 sec)
为什么该上面语句执行结果为空？null 表示什么也不是不存在， 不能=、>、< … 所有的判断，结果都是false，所有只能用 is null进行判断。
默认情况下，推荐使用 IS NOT NULL去判断，因为SQL默认情况下对！= Null的判断会永远返回0行，但没有语法错误

select * from config_dns_set where set_dns is not null;
+----+---------+-----------------+--------+---------------------+
| id | casenum | set_dns         | switch | edit_time           |
+----+---------+-----------------+--------+---------------------+
|  1 |     100 | 114.114.114.114 |      0 | 2018-01-01 00:00:00 | 
|  2 |     100 | 114.114.114.114 |      1 | 2018-02-01 00:00:00 | 
|  3 |     100 | 114.114.114.114 |      0 | 0000-00-00 00:00:00 | 
|  4 |     100 |                 |      0 | 0000-00-00 00:00:00 | 
|  5 |       0 |                 |      0 | 0000-00-00 00:00:00 | 
|  6 |       0 |                 |      0 | 0000-00-00 00:00:00 | 
|  7 |       0 | null            |      0 | 0000-00-00 00:00:00 | 
+----+---------+-----------------+--------+---------------------+
7 rows in set (0.00 sec)
第7条记录实际是插入的'null'字符，并不是不存在的NULL，一般系统默认显示为NULL的才是真正不存在。