SQL的一些问题

如何count null值？

当列所在行值为 null 时，count(列名) 是不会把 null 值计算出来的。而count(*) , count(1) 等方式是会计算的。
即使我们设置了非null 在join时候仍然会遇到count null的问题

比如null_column

解决方法1：筛选出所选的数据后，不通过count(null_column)而通过 count(id)的方法。缺点是当多个count的时候 不奏效。

解决方法2 case语法 如

SELECT sum(case when (null_column=xx or null_column is null) then 1 else 0 end)as count

性能有区别么？

解决方法3–无法解决 但也许能通过更复杂的方式写出来 由于下面的方法更简单 放弃接着这样

count(null_column=xx OR NULL) 无法得到正确结果，这里的null的含义并非是null_column等于0或为空，而是因为null_column不等于xx时，结果为false，而count(false)=1,只有count(null)=0,所以这里null的意思是，当不等于的时候，变成count(null)。
ps1 但是，在mysql中，两者一样，所以此方法似乎不能求出来。
PS2 由于mysql和sql有区别，尽量避免仅属于mysql的写法
PS3 mysql VS sql server ；sql仅仅是语言 但是mysql的语法有些特殊的 比如using

解决方法3 --可以解决 改count为sum

sum(null_column=xx OR null_column is null)as count

但是conut的性能似乎比sum要好
差距大么？

count(if(xxx,null)) as clock_count  可以这样

如何count group_by的数量？

方法1 在外面再包一层count查询

SELECT count(*) from (SELECT count(user_order_id),user_order_id FROM `item_order` WHERE seller_id=24 GROUP BY user_order_id ORDER BY user_order_id DESC LIMIT 30 OFFSET 0)

方法2
想不通上面的回答为什么都是再套一层!
说说我的理解：

SELECT COUNT(DISTINCT user_order_id) FROM item_order WHERE seller_id=24

！=或者<>自动过滤null

在测试数据时忽然发现，使用如下的SQL是无法查询到对应column为null的数据的：

select * from test where name != ‘Lewis’;
本意是想把表里name的值不为Lewis的所有数据都搜索出来，结果发现这样写无法把name的值为null的数据也包括进来。

上面的!=换成<>也是一样的结果，这可能是因为在数据库里null是一个特殊值，有自己的判断标准，如果想要把null的数据也一起搜索出来，需要额外加上条件，如下：

select * from test where name != ‘Lewis’ or name is null;
虽然这只是个小知识点，不过还是值得记录注意下，以免日后在开发中犯小错误

分组获取总数

group_by的 无法直接获取分组的总数 只能获取单个的

SELECT COUNT(*) 

FROM （SELECT 分组字段 FROM 表 

GROUP BY 分组字段 

）别名 

或者 

SELECT COUNT(*) 

FROM （SELECT distinct 分组字段 FROM 表）别名

Expression #1 of SELECT list is not in GROUP BY clause 解决方案

1.描述：
数据库中的表grades为

id	 |	gro	| name	| score
---------------------------
1	 |	A	| 张三	| 20
2	 |	B	| 李四	| 19
3	 |  B	| 王五	| 17
4	 |  C	| 赵六	| 18

SQL语句：

select name, gro from grades group by gro

返回：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘grades.name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

同样的SQL语句，在mysql5.5.43的数据库中报错，在5.7.5的mysql数据库中却正常。

2.分析：

对于上述的报错信息，在下的理解是select字段里包含了没有被group by条件唯一确定的字段name。

因为执行sql_make_group语句实际上把两行纪录李四和王五合并成一行，搜索引擎不知道该返回哪一条，所以报错。

在5.7之前会随机选择一条进行展示

3.原因：
MySQL 5.7.5后only_full_group_by成为sql_mode的默认选项之一，这可能导致一些sql语句失效。

这个错误会发生在mysql 5.7 版本及以上版本mysql 5.7版本以上默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"。
很多程序员从5.6升级到5.7时，为了语法兼容，大部分会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。

2）sql层面

在sql执行时，出现该原因，简单来说就是：
由于开启了ONLY_FULL_GROUP_BY的设置，如果select 的字段不在 group by 中，
并且select 的字段未使用聚合函数（SUM,AVG,MAX,MIN等）的话，那么这条sql查询是被mysql认为非法。

4 验证
1）查询数据库版本

SELECT VERSION();//大于5 .7

2）查看sql_mode

select @@GLOBAL.sql_mode;

sql_mode的值

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

显然，sql_mode开启了ONLY_FULL_GROUP_BY属性。

5.解决方法：

方案一
使用ANY_VALUE()包含非group by 字段

SELECT ANY_VALUE(id),user_id FROM feedback GROUP BY user_id;

由此可见，问题轻松解决！

ANY_VALUE()函数说明

MySQL有any_value(field)函数，它主要的作用就是抑制ONLY_FULL_GROUP_BY值被拒绝。
这样sql语句不管是在ONLY_FULL_GROUP_BY模式关闭状态还是在开启模式都可以正常执行，不被mysql拒绝。
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据。
官方有介绍，地址：https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

方案二
通过sql语句暂时性修改sql_mode，即去掉ONLY_FULL_GROUP_BY属性

SET @@global.sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;
1
上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据库下执行：

SET sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;
1
这种方案有个问题：重启mysql数据库服务之后，ONLY_FULL_GROUP_BY还会出现，所以这只是暂时性的。

方案三
通过配置文件永久修改sql_mode
1）Linux下修改配置文件
编辑my.cnf文件，文件地址一般在：/etc/my.cnf，/etc/mysql/my.cnf
找到sql_mode，将其中的ONLY_FULL_GROUP_BY属性去掉，如果没有sql_mode配置，就在 [mysqld]下面追加一下即可。

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1
保存之后一定要重启mysql

service mysql restart
1
2)window下修改配置文件
找到mysql安装目录，用记事本直接打开my.ini文件
同理，追加sql_mode，如下：

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1

注意事项
永久修改sql_mode后，不管是linux还是windows都要重启mysql服务才会生效

service mysql restart

OR

vim /etc/mysql/conf.d/mysql.cnf

在配置文件my.cnf中关闭sql_mode=ONLY_FULL_GROUP_BY.。msqyl的默认配置是sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION。可以把ONLY_FULL_GROUP_BY去掉，也可以去掉所有选项设置成sql_mode=，如果你确信其他选项不会造成影响的话。

复制并粘贴，如有[mysqld]，则放到 [mysqld]最后，如没有则放到配置文件最下方即可

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

wq保存并退出

重启MySQL

service mysql restart

暂未分类

1、substr的索引是从1开始的 而非0
2、当我们想复制某些表的某些字段到另一个表的时候，应该区分是新增还是update
2.1 insert

insert into user_info_related(uid,wx_m_openid,wx_unionid) 
select id,wx_s_openid,wx_unionid 
from user_info;

2.2、用update复制可能出错

insert user_info_related ,user_info 
SET user_info_related.wx_m_openid = user_info.wx_s_openid

where条件加上主键就好了 where … and A.id > 0
MySql运行在safe-updates模式下,导致非主键条件下无法执行update或者delete命令

3、当我们使用join，但是有两个相同的字段（但是其值不一定相同），比如A.sex，B.sex，通过name=name连接。此时筛选A.sex=男，如果选择的列为全部的话，那么最后的结果只有一个sex（B的），你会发下where条件失效 sex可能有女。所以在写where的时候，还得加上B.sex=女