标签搜索

Mysql

NingShuYang
2022-07-13 / 0 评论 / 176 阅读 / 正在检测是否收录...

查流程:

show processlist;

授权:

grant all privileges on . to 'root'@'%' identified by '密码' with grant option;
flush privileges;
白名单例:
180.109.%.% 百度搜ip查看掩码

删旧用户

use mysql;
select user,host from user;
delete from user where user='' and host ='';

递归sql

group_concat默认长度1M超出自动截取,手动改100M解决丢失问题
在MySQL配置文件中my.conf或my.ini中[mysqld]里添加:
group_concat_max_len=102400

向上递归

SELECT T2.area_id
FROM (
         SELECT @r                                                      AS _id,
                (SELECT @r := pid FROM sys_area aa WHERE area_id = _id) AS parent_id,
                @l := @l + 1                                            AS lvl
         FROM (SELECT @r := 自己的ID, @l := 0) vars,
              sys_area h
         WHERE @r <> 0) T1
         JOIN sys_area T2
              ON T1._id = T2.area_id

向下递归

  SELECT DATA.area_id
FROM (
         SELECT @ids                           AS _ids,
                (SELECT @ids := GROUP_CONCAT(area_id)
                 FROM sys_area
                 WHERE enabled = true
                   and FIND_IN_SET(pid, @ids)) AS cids,
                @l := @l + 1                   AS LEVEL
         FROM sys_area,
              (SELECT @ids := 自己的ID, @l := 0) b
         WHERE @ids IS NOT NULL
     ) ID,
     sys_area DATA
WHERE enabled = true
  and FIND_IN_SET(DATA.area_id, ID._ids)
  and area_id != 自己的ID
0

评论 (0)

取消