mysql常用但易忘东西记录

1、find_in_set(str, strList)（查询字段(strlist)中包含(str)的结果，返回结果为null或记录）

select dict_name from sys_dict where find_in_set(dict_id, (select parent_ids from sys_dict where state_num=1 and dict_id = 100))

2、group_concat（将查询出的同一字段多条结果拼成一个字符串，各结果中间使用英文逗号隔开）

select group_concat(dict_name) from sys_dict where dict_id in (select dict_id from sys_dict where state_num=1 and dict_id < 10)

 3、collate 解决两个表连接查询不一致导致报错的问题（如报  Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' 的错误时，强转成utf8mb4_general_ci字符集）

select su.user_name, su.age, sd.dict_name from sys_user su left join sys_dict sd on (su.user_name = sd.dict_value collate utf8mb4_general_ci) and sd.state_num=1 where su.state_num=1 and user_id = 1

 4、union all 解决两个集合合并的问题，（注意：两select 必选加limit，否则会按顺序排列）

select p4.project_id from (
    (
        select p.project_id from (
            select co.project_id, p1.create_time, count(co.order_id) as sales_num from crm_order co
            inner join policy_project p1 on p1.project_id = co.project_id and p1.state_num = 1
            where co.state_num = 1 group by co.project_id
        ) as p order by p.sales_num desc, p.create_time desc limit 99999999  // 注意，如果不加上limit的话两个结果集会按主键顺序排序
    ) union all
    (
        select p2.project_id from 
        (
            select p3.project_id from policy_project p3 where p3.project_id not in (
                select co1.project_id from crm_order co1 where co1.state_num = 1
            ) and p3.state_num = 1 order by p3.create_time limit 99999999 // 必选加limit，否则会按主键排序
        ) as p2
    )
) as p4 

 4、order by field(字段， 值1，值2，值3...) 根据给定的数值来排序。注意（如果给定的数值不是根据条件查询出来的所有结果，则会先查出没有给定值的结果，才会查给定值的结果）

select order_id from crm_order where state_num =1 order by field(order_id, 3,5,6,8,10) 

 5、centos、MySQL同时使用group by 和order by查询报1055, "Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"错误：

# 查看当前规则：
1、select @@sql_mode;
2、在/etc/mysql/conf.d/mysql.cnf （若没有此文件，则找/etc/my.cnf）底部加上：
sql_mode = 1的查询结果（去掉：only_full_group_by）
3、登录mysql
flush privileges;
4、重启mysql
systemctl restart mysqld

6、mysql8设置密码规则为简单规则：

# 如果是刚安装的mysql库需要先修改初始密码
# 获取初始密码
cat /var/log/mysqld.log |grep 'A temporary password'
mysql -u 'root' -p
#复制粘贴初始密码
# 先设置一个符合当前规则的密码，否则无法修改密码规则
alter user 'root'@'localhost' identified by 'Abc123456,./';

# 查看当前密码规则
SHOW VARIABLES LIKE 'validate_password%';
# 设置密码规则
# 设置密码可以与名称相同
set global validate_password.check_user_name=0;
# 设计简单密码
set global validate_password.policy=0;
# 设置最少密码长度
set global validate_password.length=4;
# 修改密码
alter user 'root'@'localhost' identified by 'root';

 7、求跨周、月、季度、年的跨度，其中 sys_login_log 可用任意表代替（表格最好是经过导入的，否则最远的跨度时间是创建表格的时间）

@ 声明变量，select @d := 0 表示初始化变量d=0， @d := @d - 1 表示一条数据 -1 

-- 年
select date_format( vDate, '%Y' ) AS vDate from (
    select adddate( curdate(), INTERVAL @d year ) AS vDate, @d := @d - 1 year
    from sys_login_log, ( select @d := 0 ) temp
) test
LIMIT 0, 5;

-- 季度
select date_format( vDate, '%Y-%m' ) AS vDate from (
    select adddate('2021-12-31', INTERVAL @d quarter ) AS vDate, @d := @d - 1 quarter
    from sys_login_log, ( select @d := 0 ) temp
) test
LIMIT 0, 4;


-- 月
select date_format( vDate, '%Y-%m' ) AS vDate from (
    select adddate( curdate(), INTERVAL @d month ) AS vDate, @d := @d - 1 month
    from sys_login_log, ( select @d := 0 ) temp
) test
LIMIT 0, 12;


-- 周
select date_format( vDate, '%Y-%m-%d' ) AS vDate from (
    select adddate( curdate(), INTERVAL @d WEEK ) AS vDate, @d := @d - 1 WEEK
    from sys_login_log, ( select @d := 0 ) temp
) test
LIMIT 0, 5;