DB小技巧

导入数据异常

表格式表存储错误

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1118 (42000) at line 3226: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
bash-4.4#

解决方法：替换成DYNAMIC即可

SUPER权限问题

ERROR 1419 (HY000) at line 7597: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
bash-4.4# mysql --default-character-set=utf8mb4 -h mysql --port 3306 -u gponline -p'b90I5mYXd3Fa9Q0Ke6hVyyTHNgd7cDPj' gponline < gponline.20241007.sql

解决方案：

#
SET GLOBAL log_bin_trust_function_creators = 1;

视图权限问题

ERROR 1227 (42000) at line 16765: Access denied; you need (at least one of) the SUPER, SET_USER_ID or SET_ANY_DEFINER privilege(s) for this operation

解决方案：

修改视图的创建者，或者给当前用户赋予SUPER权限。

MySQL清除Binlog日志

清除后，日志文件会自动删除。释放掉日志文件占用的空间。

指定日期

PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

清除所有

PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

MySQL定时器

查看定时器是否开启

提示

确认定时器是否开启很重要，否则定时器不会执行。

show variables like 'event_scheduler';

开启定时器

如果是阿里云的RDS，如要在RDS控制台 > 参数设置 中进行操作

SET GLOBAL event_scheduler = NO;

查看定时器列表

show events;

添加定时器

CREATE EVENT my_event
ON SCHEDULE
    EVERY 1 HOUR
DO
    -- 在这里写要执行的 SQL 语句
    INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2');

启动定时器

ALTER EVENT my_event ENABLE;

禁用定时器

ALTER EVENT my_event DISABLE;

修改定时器

ALTER EVENT my_event
ON SCHEDULE
    EVERY 2 HOURS
DO
    -- 修改要执行的 SQL 语句
    UPDATE my_table SET column1 = 'new_value' WHERE column2 = 'some_condition';

删除定时器

DROP EVENT my_event;

MySQL递归查询

参考:

• mysql递归查询

# mysql递归查询
# 查询指定ID(100029)的所有父节点
SELECT id,nickname,pid
    FROM ( 
        SELECT 
                @r AS _id, 
                (SELECT @r := pid FROM av_user WHERE id = _id) AS parent_id, 
                 @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := 100029, @l := 0) vars, 
                av_user h 
        WHERE @r <> 0) T1 
    JOIN av_user T2 
    ON T1._id = T2.id
ORDER BY id;

# 多条数据在一行
SELECT GROUP_CONCAT(id) pids
    FROM ( 
        SELECT 
                @r AS _id, 
                (SELECT @r := pid FROM av_user WHERE id = _id) AS parent_id, 
                 @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := 100029, @l := 0) vars, 
                av_user h 
        WHERE @r <> 0) T1 
    JOIN av_user T2 
    ON T1._id = T2.id
ORDER BY id;

MySQL强制查询不使用缓存

# 添加sql_no_cache指令即可
select sql_no_cache * from dual;

MySQL关联更新

# 关联更新
update tab_a a join tab_b b on a.b_id = b.id 
set a.b_name = b.name, a.b_age = b.age
where a.id >= 100;