MySQL问题解决方案合集

MySQL问题解决方案合集

only_full_group_by错误解决

问题说明：
-- MySQL5.7以上版本，默认开启了only_full_group_by模式：
> select @@global.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

开启这个模式后，不符合的group by语句就会报错：
SELECT list is not in GROUP BY clause and contains nonaggregated column 'test' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因分析：
MySQL在低版本（5.7.x以下）中允许select后面的非聚合列不出现在group by中。以下sql在低版本中是可以运行的，但是在5.7及以上版本会报错：
select name, age, count(name) from student group by age

1）order by后面的列必须是在select中存在。
2）select、having或order by后面存在的非聚合列必须全部在group by中存在。
3）而没有遵循原则的sql会被认为是不合法的sql，如SQLServer、Oracle、PostgreSql都不支持select target list中出现语义不明确的列，MySQL 5.7开始修正，即ONLY_FULL_GROUP_BY语义。

解决方案：
方案一：保持only_full_group_by模式开启
MySQL提供了any_value(field)函数允许非分组字段的出现（和关闭 only_full_group_by 模式有相同效果）。
select any_value(name), age, count(name) from student group by age

方案二：关闭only_full_group_by模式
1）当前会话关闭失效
> select @@sql_mode
复制查询出来的值，去除only_full_group_by，再设置回去：
> set sql_mod='去除only_full_group_by后的值'
比如：在navicat中当前窗口执行，在当前窗口生效，新开窗口失效。

2）当前服务关闭失效
> select @@global.sql_mode
复制查询出来的值，去除only_full_group_by，再设置回去：
> set @@global.sql_mode='去除only_full_group_by后的值'

3）永久生效
修改MySQL配置文件my.conf，然后重启MySQL。
[mysqld]    
sql_mode=去除only_full_group_by后的值

4）JDBC参数关闭only_full_group_by
jdbc:mysql://localhost:3306/test?sessionVariables=sql_mode='去除only_full_group_by后的值'

在Mybatis的xml文件调用Java类的方法

使用的是OGNL表达式，表达式格式为：${@prefix@methodName(传递参数名称)}
1）赋值时判断
SELECT * FROM USER WHERE name='${@org.apache.commons.lang3@StringUtils(name)}' 

2）IF条件判断
<if test="@org.apache.commons.lang.StringUtils@isNotBlank(name)">name=#{name}</if>

3）自定义生成ID
<insert id="importData">
        insert into myproperty (id,asset_id,asset_name,dept_name,brand,specifications,units,produce_time,indate,purchase_time,price,
        total_money,address,classification,is_fixed) values
        <foreach collection="list" item="property" separator=",">
            (
            ${@cn.hutool.core.util.IdUtil@getSnowflake(1, 3).nextIdStr()},
            ${@cn.hutool.core.util.IdUtil@getSnowflake(1, 3).nextId()},
            #{property.assetName},
            #{property.deptName},
            #{property.brand},
            #{property.specifications},
            #{property.units},
            #{property.produceTime},
            #{property.indate},
            #{property.purchaseTime},
            #{property.price},
            #{property.price},
            #{property.address},
            #{property.classification},
            <choose>
                <when test="property.price > 1000">
                    '0'
                </when>
                <otherwise>
                    ''
                </otherwise>
            </choose>
            )
        </foreach>
</insert>

MySQL时间处理相关

MySQL根据出生日期统计各年龄段人数

按天计算年龄

SELECT
	NAME,
	COUNT(NAME) AS VALUE
FROM
	(
        SELECT
        CASE
        WHEN age < 20 THEN	'19岁及以下'
        WHEN age <= 29 THEN	'20-29岁'
        WHEN age <= 39 THEN	'30-39岁'
        WHEN age <= 49 THEN	'40-49岁'
        ELSE	'50岁及以上'
        END AS NAME
        FROM
        (
            select
            TIMESTAMPDIFF(YEAR,p.birthday,CURDATE()) AS age
            from
            ipms_person p WHERE p.birthday is not NULL
        ) t
    )t GROUP BY NAME

按年份计算年龄

SELECT
	NAME,
	COUNT(NAME) AS VALUE
FROM
	(
        SELECT
        CASE
        WHEN age < 20 THEN	'19岁及以下'
        WHEN age <= 29 THEN	'20-29岁'
        WHEN age <= 39 THEN	'30-39岁'
        WHEN age <= 49 THEN	'40-49岁'
        ELSE	'50岁及以上'
        END AS NAME
        FROM
        (
            select
            DATE_FORMAT(NOW(),'%Y') -substr(p.birthday,1,4) AS age
            from
            ipms_person p WHERE p.birthday is not NULL
        ) t
    )t GROUP BY NAME

MySQL根据时间统计数据

-- 查询当天数据
SELECT * FROM 表名 WHERE TO_DAYS(时间字段名) = TO_DAYS(NOW());

-- 查询昨天数据
SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段名) = 1

-- 查询当前这周的数据
SELECT * FROM 表名 WHERE YEARWEEK( DATE_FORMAT(时间字段名, '%Y-%m-%d')) = YEARWEEK(NOW());

-- 查询上周的数据
SELECT * FROM 表名 WHERE YEARWEEK( DATE_FORMAT(时间字段名, '%Y-%m-%d')) = YEARWEEK(NOW()) - 1;

-- 查询距离当前现在6个月的数据
SELECT * FROM 表名 WHERE 时间字段名 BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();

-- 查询近7天数据
SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(时间字段名)

-- 查询近30天数据
SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(时间字段名)

-- 查询当月数据
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')

-- 查询上一月数据
SELECT * FROM 表名 WHERE PERIOD_DIFF( DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(时间字段名, '%Y%m')) = 1

-- 查询本季度数据
SELECT * FROM 表名 WHERE QUARTER (时间字段名) = QUARTER (NOW());

-- 查询上季度数据
SELECT * FROM 表名 WHERE QUARTER (时间字段名) = QUARTER ( DATE_SUB(NOW(), INTERVAL 1 QUARTER));

-- 查询本年数据
SELECT * FROM 表名 WHERE YEAR (时间字段名) = YEAR (NOW());

-- 查询上年数据
SELECT * FROM 表名 WHERE YEAR (时间字段名) = YEAR ( date_sub(NOW(), INTERVAL 1 YEAR));

MySQL查询每个月每一天的访问量

最近在公司做报表，需要查询每个月每一天的访问数量然后做成折线统计图，鉴于此，写下SQL实现过程，说明：这是查询每个月每一天的某个路径被访问的数量，aqMng表示访问路径，e表示每一天，07表示7月，注意表中要存在log_time字段：
select count(*),DATE_FORMAT(a.log_time,'%e') as e,a.log_method from sys_log a
where 1=1 and DATE_FORMAT(a.log_time,'%m')='07' and log_method='aqMng'
group by e,a.log_method order by e

DATE_FORMAT函数扩展：
DATE_FORMAT(date,format)
date参数是合法的日期，format规定日期/时间的输出格式。

下面的脚本使用DATE_FORMAT()函数来显示不同的格式，我们使用NOW()来获得当前的日期/时间：
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') 
DATE_FORMAT(NOW(),'%m-%d-%Y') 
DATE_FORMAT(NOW(),'%d %b %y') 
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
返回结果：
Dec 29 2008 11:45 PM 
12-29-2008 29 
Dec 08 29 
Dec 2008 16:25:46.635

DATE_FORMAT格式对照表：

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

MySQL统计某个表每天的总量和增量

-- 创建人脸信息表
CREATE TABLE `ipms_person_face` (
    `ID` varchar(32) NOT NULL COMMENT '人脸主键',
    `NAME` varchar(32) DEFAULT NULL COMMENT '人员姓名',
    `SEX` int(11) DEFAULT NULL COMMENT '性别 GB/T2261.1 性别代码：0未知的性别，1男性，2女性，9未说明的',
    `SOURCE` int(11) DEFAULT NULL COMMENT '人脸来源 0物管平台，1移动端',
    `COLLECT_TIME` datetime DEFAULT NULL COMMENT '采集时间',
    `FACE` text COMMENT '人脸原图地址',
    `SMALL_FACE` text COMMENT '人脸小图地址',
    `FACE_TAG` varchar(1024) DEFAULT NULL COMMENT '人脸标签',
    `COMMUNITY_TAG` varchar(128) DEFAULT NULL COMMENT '小区标签 多个逗号相隔',
    `CREATE_BY` varchar(32) DEFAULT NULL COMMENT '创建人',
    `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间',
    `UPDATE_BY` varchar(32) DEFAULT NULL COMMENT '更新人',
    `UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间',
    `DEL_FLAG` char(1) DEFAULT '0' COMMENT '删除标识 0未删除，1已删除',
    `TENANT` varchar(255) DEFAULT NULL COMMENT '租户标识',
    `STATUS` char(1) DEFAULT '0' COMMENT '驳回标识 0正常，1已驳回',
    `FACE_ANGLE` varchar(45) DEFAULT NULL COMMENT '人脸角度',
    PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='人脸信息 ';

根据人脸采集时间，获取每天采集的人脸数和每天的人脸总数

SELECT
	a.dd '时间',
	a.addNum '每天增加',
	a.allNum '每天总数（包括之前）'
FROM
	(
        SELECT
        a.dd,
        a.addNum,
        SUM( b.addNum ) AS allNum 
        FROM
        ( SELECT DATE_FORMAT( COLLECT_TIME, '%Y-%m-%d' ) dd, COUNT(*) addNum FROM ipms_person_face GROUP BY DATE_FORMAT( COLLECT_TIME, '%y-%m-%d' ) ) a
        JOIN ( SELECT DATE_FORMAT( COLLECT_TIME, '%Y-%m-%d' ) dd, COUNT(*) addNum FROM ipms_person_face GROUP BY DATE_FORMAT( COLLECT_TIME, '%y-%m-%d' ) ) b ON a.dd >= b.dd 
        GROUP BY
        dd 
        ORDER BY
        dd DESC 
    ) a

MySQL向数据库表插入当前时间

-- Mysql中写法：
-- 直接调用NOW()函数就能获取当前时间，然后直接执行插入即可
-- 例：2008-11-11 12:45:34	
insert into table (id,create_time) values(1,NOW());

-- 或者使用CURDATE()，获取的是当前"年-月-日"时间
-- 例：2008-11-11
insert into table (id,create_time) values(2,CURDATE());

-- 或者使用CURTIME()，获取的是当前"时:分:秒"时间
-- 例：12:45:34
insert into table (id,create_time) values(3,CURTIME());

-- Oracle中写法:
-- 使用SYSDATE，获取的是当前的“年-月-日 时:分:秒”时间
--例：2022-2-15 11:11:11
insert into table (id,create_time) values(4,SYSDATE);

MySQL根据ID查询树结构所有父子节点

新建表结构并初始化数据

CREATE TABLE `monitor_menu` (
  `id` varchar(45) NOT NULL DEFAULT '0' COMMENT '主键ID',
  `menu_name` varchar(45) DEFAULT NULL COMMENT '名称',
  `parent_id` varchar(45) DEFAULT NULL COMMENT '父级ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='树结构数据表';

INSERT INTO `jsbz`.`monitor_menu`(`id`, `menu_name`, `parent_id`) VALUES ('1001', '一级目录', '0');
INSERT INTO `jsbz`.`monitor_menu`(`id`, `menu_name`, `parent_id`) VALUES ('1002', '二级目录1', '1001');
INSERT INTO `jsbz`.`monitor_menu`(`id`, `menu_name`, `parent_id`) VALUES ('1003', '二级目录2', '1001');
INSERT INTO `jsbz`.`monitor_menu`(`id`, `menu_name`, `parent_id`) VALUES ('1004', '二级目录3', '1001');
INSERT INTO `jsbz`.`monitor_menu`(`id`, `menu_name`, `parent_id`) VALUES ('1005', '三级目录1', '1002');
INSERT INTO `jsbz`.`monitor_menu`(`id`, `menu_name`, `parent_id`) VALUES ('1006', '三级目录2', '1003');
INSERT INTO `jsbz`.`monitor_menu`(`id`, `menu_name`, `parent_id`) VALUES ('1007', '三级目录3', '1004');

根据指定ID查询所有父节点数据

-- monitor_menu：树结构表
-- 1005：指定查询ID

SELECT
	T2.* 
FROM
	(
	SELECT
		@r AS _id,
		( SELECT @r := parent_id FROM monitor_menu WHERE id = _id ) AS parent_id,
		@l := @l + 1 AS lvl 
	FROM
		( SELECT @r := '1005', @l := 0 ) vars,
		monitor_menu h 
	WHERE
		@r <> 0 
	) T1
	JOIN monitor_menu T2 ON T1._id = T2.id 
ORDER BY
	T1.lvl DESC

根据指定ID查询所有子节点数据

-- monitor_menu：树结构表
-- 1001：指定查询ID
-- 创建一个存储过程

DROP FUNCTION IF EXISTS `getChildrenList`;
CREATE FUNCTION `getChildrenList` (rootId VARCHAR ( 1000 )) 
RETURNS VARCHAR ( 1000 ) 
BEGIN
	DECLARE
		sTemp VARCHAR ( 1000 );
	DECLARE
		sTempChd VARCHAR ( 1000 );
	
	SET sTemp = '$';
	SET sTempChd = cast( rootId AS CHAR );
	
	WHILE sTempChd IS NOT NULL DO
		SET sTemp = concat( sTemp, ',', sTempChd );
		SELECT
			group_concat( id ) INTO sTempChd 
		FROM
			monitor_menu 
		WHERE
			FIND_IN_SET( parent_id, sTempChd )> 0;
	END WHILE;
	RETURN sTemp;
END

执行SQL查询：
select * from monitor_menu where FIND_IN_SET(id,getChildrenList('1001'))

MySQL使用group by分组后对某个字段值拼接成字符串

SELECT
	c.imo,
	c.adminUserIds,
	c.adminUserNames,
	d.viewUserIds,
	d.viewUserNames 
FROM
	(
	SELECT
		a.imo imo,
		group_concat( a.user_id ) adminUserIds,
		group_concat( b.username ) adminUserNames 
	FROM
		sys_users_vessel a
		LEFT JOIN sys_user b ON a.user_id = b.user_id 
	WHERE
		a.role_type = 0 
	GROUP BY
		imo 
	) c
	INNER JOIN (
	SELECT
		a.imo imo,
		group_concat( a.user_id ) viewUserIds,
		group_concat( b.username ) viewUserNames 
	FROM
		sys_users_vessel a
		LEFT JOIN sys_user b ON a.user_id = b.user_id 
	WHERE
		a.role_type = 1 
	GROUP BY
		imo 
	) d ON c.imo = d.imo 
WHERE
	c.imo = '9799197' 
	AND adminUserNames LIKE concat( '%', 'XDZY', '%' ) 
	OR viewUserNames LIKE concat( '%', 'XDZY', '%' );