mysql注意事项

文章目录

存储过程

更新了0条数据是不会报错的；
查询单条数据，没有匹配的会报“sql: no rows in result set”；查询多条数据，没有匹配的会返回空数组
对于唯一键，同时有几个接口都在插入数据，会报错“Duplicate entry * for key”

Mysql保持数据一致性的方法：加事务；mq补偿
https://blog.51cto.com/toren/5427075
https://blog.csdn.net/wangchengming1/article/details/120409137

mlysql幂等是啥

set @@session.sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
设置严格模式'ONLY_FULL_GROUP_BY时：
有group by时，不能select 聚合列(group by的列)以外的列，单独select聚合函数avg(),sum(),count(),min(),max()时,不能同时select任何列

"Out of sort memory error " has an inconsistent relationship with the buffer size
mysql版本 8.0.29 排序缓冲大小256k -> 1M，并且改为内存里排序，可以解决存的json字段太大导致排序失败查出空的错误；
内存排序实现sort方法

type Uint64Slice []uint64

func (p Uint64Slice) Len() int           { return len(p) }
func (p Uint64Slice) Less(i, j int) bool { return p[i] < p[j] }
func (p Uint64Slice) Swap(i, j int)      { p[i], p[j] = p[j], p[i] }

sort.Sort(utils.Uint64Slice(userIdList))

1
2
3
4
5
6
7
8

当前读和快照读 https://juejin.cn/post/7001357238648438821
mvcc https://juejin.cn/post/7001792991073402916

b树和b+树及红黑树的区别 https://www.jianshu.com/p/ace3cd6526c4

存储过程

-- 创建一个存储过程
DROP PROCEDURE IF EXISTS PixsoWebAddColumnUnlessExists;

CREATE PROCEDURE PixsoWebAddColumnUnlessExists(
    IN tableName tinytext,
    IN fieldName tinytext,
    IN fieldDef text)
BEGIN
    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = fieldName
              AND table_name = tableName
              AND table_schema = database()
        )
    THEN
        SET @ddl = CONCAT('ALTER TABLE ', tableName, ' ADD COLUMN ', fieldName, ' ', fieldDef, ';');
        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
    END IF;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- 创建一个存储过程
DROP PROCEDURE IF EXISTS Temp_20230211_DDL_user_view_menu;

CREATE PROCEDURE Temp_20230211_DDL_user_view_menu()
BEGIN
    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = 'plugin_dock'
              AND table_name = 'user_view_menu'
              AND table_schema = DATABASE()
        )
    THEN
        ALTER TABLE `user_view_menu`
            ADD `plugin_dock` json NULL COMMENT '程序坞属性';
    END IF;

END;

-- 使用方法
CALL Temp_20230211_DDL_user_view_menu();

-- 不再需要可删除
DROP PROCEDURE Temp_20230211_DDL_user_view_menu;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- 创建一个存储过程
DROP PROCEDURE IF EXISTS Temp_20221207_DDL_person_folder;

CREATE PROCEDURE Temp_20221207_DDL_person_folder()
BEGIN
    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = 'type'
              AND table_name = 'role_team'
              AND table_schema = DATABASE()
        )
    THEN
        ALTER TABLE `role_team`
            ADD COLUMN `type` smallint NOT NULL DEFAULT 1 COMMENT '团队类型;1:普通团队;2我的草稿';
    END IF;

    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = 'type'
              AND table_name = 'role_folder'
              AND table_schema = DATABASE()
        )
    THEN
        ALTER TABLE `role_folder`
            ADD COLUMN `type` smallint NOT NULL DEFAULT 1 COMMENT '项目类型;1:普通项目;2:草稿下的项目';
    END IF;

    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = 'type'
              AND table_name = 'folder'
              AND table_schema = DATABASE()
        )
    THEN
        ALTER TABLE `folder`
            ADD COLUMN `type` smallint NOT NULL DEFAULT 1 COMMENT '项目类型;1:普通项目;2:草稿下的项目';
    END IF;

    UPDATE `role_team` ,`team` SET `role_team`.`type`=2 WHERE `role_team`.`team_id`=`team`.`id` AND  `team`.`name` = ''  ;

    UPDATE `folder` ,`team` SET `folder`.`type`=2 WHERE `folder`.`team_id`=`team`.`id` AND  `team`.`name`=''  ;

    UPDATE `role_folder` ,`folder` SET   `role_folder`.`type` = `folder`.`type`   WHERE `role_folder`.`folder_id`=`folder`.`id` AND `folder`.`type`=2;
END;

-- 使用方法
CALL Temp_20221207_DDL_person_folder();

-- 不再需要可删除
DROP PROCEDURE Temp_20221207_DDL_person_folder;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

-- 创建一个存储过程
DROP PROCEDURE IF EXISTS Temp_20221202_DDL_team;

CREATE PROCEDURE Temp_20221202_DDL_team()
BEGIN
    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = 'avatar_status'
              AND table_name = 'team'
              AND table_schema = DATABASE()
        )
    THEN
        ALTER TABLE `team`
            ADD COLUMN `avatar_status` VARCHAR(16) NOT NULL DEFAULT 'pass' COMMENT '团队头像审核状态';
    END IF;
END;

-- 使用方法
CALL Temp_20221202_DDL_team();

-- 不再需要可删除
DROP PROCEDURE Temp_20221202_DDL_team;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

DROP PROCEDURE IF EXISTS del_index;

-- 创建一个存储过程
DROP PROCEDURE IF EXISTS Temp_20230210_DDL_plugin;

CREATE PROCEDURE Temp_20230210_DDL_plugin()
BEGIN
    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = 'set_panel_cover'
              AND table_name = 'plugin_audit'
              AND table_schema = DATABASE()
        )
    THEN
        ALTER TABLE `plugin_audit`
            ADD `set_panel_cover` int DEFAULT 0 NOT NULL COMMENT '插件面板是否增加配图' AFTER `summary`;
    END IF;

    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = 'set_panel_cover'
              AND table_name = 'plugin'
              AND table_schema = DATABASE()
        )
    THEN
        ALTER TABLE `plugin`
            ADD `set_panel_cover` int DEFAULT 0 NOT NULL COMMENT '插件面板是否增加配图' AFTER `summary`;
    END IF;
END;

-- 使用方法
CALL Temp_20230210_DDL_plugin();

-- 不再需要可删除
DROP PROCEDURE Temp_20230210_DDL_plugin;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- 创建一个存储过程
DROP PROCEDURE IF EXISTS Temp_20230111_DDL_plugin_tag_meta;

CREATE PROCEDURE Temp_20230111_DDL_plugin_tag_meta()
BEGIN
    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = 'translation'
              AND table_name = 'plugin_tag_meta'
              AND table_schema = DATABASE()
        )
    THEN
        ALTER TABLE `plugin_tag_meta`
            ADD `translation` json NULL COMMENT '多语言翻译';
    END IF;
END;

-- 使用方法
CALL Temp_20230111_DDL_plugin_tag_meta();

-- 不再需要可删除
DROP PROCEDURE Temp_20230111_DDL_plugin_tag_meta;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 创建删除索引的存储过程
DROP PROCEDURE IF EXISTS del_index;
CREATE PROCEDURE del_index(IN target_table_name VARCHAR(100),IN target_index_name VARCHAR(100))
BEGIN
    IF EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema = DATABASE()  AND table_name = target_table_name AND index_name = target_index_name) THEN
        set @statement =( select concat ('alter table ',target_table_name, ' drop key ',target_index_name ));
        PREPARE STMT FROM @statement;
        EXECUTE STMT;
    END IF;
END;

call del_index('tag','name');
call del_index('tag','idx_name_ttype');

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 创建一个存储过程
DROP PROCEDURE IF EXISTS Temp_20230102_DDL_tag;

CREATE PROCEDURE Temp_20230102_DDL_tag()
BEGIN
    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = 'translation'
              AND table_name = 'tag'
              AND table_schema = DATABASE()
        )
    THEN
ALTER TABLE `tag`
    ADD `translation` json NULL COMMENT '多语言翻译';
END IF;

    IF NOT EXISTS(
            SELECT *
            FROM information_schema.COLUMNS
            WHERE column_name = 'position'
              AND table_name = 'tag'
              AND table_schema = DATABASE()
        )
    THEN
ALTER TABLE `tag`
    ADD `position` bigint UNSIGNED DEFAULT 0 NOT NULL COMMENT '排序';
END IF;

UPDATE `tag` SET `name` = 'UI Component Set' WHERE `name` = 'UI Component Sets';
END;

-- 使用方法
CALL Temp_20230102_DDL_tag();

-- 不再需要可删除
DROP PROCEDURE Temp_20230102_DDL_tag;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

相关阅读:
报错——warning: ignoring JAVA_HOME=/home/jdk/jdk1.8.0_281； using bundled JDK
学习太极创客 — MQTT（四）服务端连接操作
 VMware 虚拟机安装 CentOS 7
代理服务器没有响应，谷歌浏览器无法上网【搬代码】
Flowable 任务如何认领，回退？
TNT平台
 SpringBoot（一、快速入门）
你好，Cartesi Rollups Alpha 0.7.0
阅读mybatis的源码的思路
 【OpenCV图像处理】图像拼接技术
原文地址：https://blog.csdn.net/Mengbabe_2018/article/details/127619296