MySql基础操作及罕见bug -持续收录

1、bug收录

1.1、SQL错误[1271][HY000]：Illegal mix of collations for operation ‘UNION’

该错误常出现于使用 ‘UNION’关键字的SQL中。

---

比较容易排查的原因为：UNION上下文字段不统一
比较隐晦的错误为：UNION所涉及的字段字符集不统一、请排查字段字符集（字符编码）

1.2、‘2022-10-01 23:59:59.999999999’ 保存或作为查询条件时变成 ‘2022-10-02 00:00’

该状况出现原因是因为mysql支持的最小秒级别为微秒，999999999超出微秒最大值，时间加了1秒

---

解决办法为把秒后续的秒调整为不大于 999999 的值
**有些版本仅支持毫秒，错误原因原理一致，请检查自己mysql版本支持的最小时间维度

1.3、bigtint 与 varchar类型字段比较时查出其它数据，例如 1300000000000020116 查出 1300000000000020119

该问题出现原因为：
1、mysql在varchar与bigtint比较时，双方都会转为浮点（双精度）数字。
2、字符串在转成浮点数字时逐位转换。
3、浮点数字在比较时是按近似值进行匹配

---

解决办法为匹配时统一类型或手动转换为同一类型后再去进行条件查询。相关连接请看：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

2、表操作

2.1、创建表

建表时字符编码UTF8的坑（生僻字乱码 - eg: 𣲗）。 问题根源：最初的 UTF-8 格式使用一至六个字节，最大能编码 31
位字符。最新的 UTF-8 规范只使用一到四个字节，最大能编码21位。在Mysql中 UTF-8只支持最长三个字节的
UTF-8字符。而很多生僻字以及Emoji表情是4字节，所以会出现无法保存的情况，需要用utf8mb4字符编码

# 直接创建
create table 表名 /* 表名 */ (
    字段名 /* 字段名 */ varchar(64) /* 字段类型 */ not null /* 不为空 */
    , 字段2 /* 字段名 */ varchar(64) /* 字段类型 */ not null /* 不为空 */ default 1 /* 默认值 */
    # 创建主键
    , primary key (字段名)
    # 唯一索引
    , unique key uk_表名_字段2 (字段2)
) ENGINE = InnoDB /* 指定数据库引擎 */ DEFAULT CHARSET = utf8 /* 指定字符编码 */;

# 不存在才创建
create table if not exists 表名 /* 表名 */ (
    字段名 /* 字段名 */ varchar(64) /* 字段类型 */ not null /* 不为空 */
    , 字段2 /* 字段名 */ varchar(64) /* 字段类型 */ not null /* 不为空 */ default 1 /* 默认值 */
    # 创建主键
    , primary key (字段名)
    # 唯一索引
    , unique key uk_表名_字段2 (字段2)
) ENGINE = InnoDB /* 指定数据库引擎 */ DEFAULT CHARSET = utf8 /* 指定字符编码 */;

2.2、查看指定表创建语句

show create table 表名;

2.3、删除表

# 直接删除
drop table 表名;
# 存在才删除
drop table if exists 表名;

2.4、修改表

# 增加字段
alter table 表名 add 字段3 varchar(64) null;
# 修改字段
alter table 表名 modify 字段3 varchar(128) null;
# 删除字段
alter table 表名 drop column 字段3;

3、时间操作

3.1、日期格式化

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 位

4、触发器

触发器可以简单的理解为一个简单的带有监听功能的sql程序，监听对象<表>数据发生相应的改变时，执行指定的内容。
在微服务，分布式流行的当下，能使用触发器的场景基本都在代码中实现，使用触发器会导致数据库移植性变差。且过于依赖数据库。从系统的角度出发，数据库作为最底层，理应保证进入数据库的数据都是正确的，规范的，允许保存的，不应该交给数据库来判断数据是否合规。
业务举例：
   1、检验新增、修改数据时，新数据内容是否符合规则。
   2、数据变化时实时汇总到相应报表内
   3、实时备份表数据到备份表（备份表只监控插入、修改）
   4、保存表数据变化日志

4.1、查看已有触发器

SHOW TRIGGERS

4.2、删除触发器

# 直接删除
DROP TRIGGER <触发器名>;
# 存在才删除
DROP TRIGGER IF EXISTS <触发器名>;

4.3、创建触发器

创建触发器注意事项
   1、触发器名称在同一个数据库中唯一
   2、触发器监控的表必须是永久性的表，不能是临时表及视图等
   3、同一张表同时刻<BEFORE|AFTER>同事件<INSERT|UPDATE|DELETE>的触发器只能有一个
解析：
a. BEFORE|AFTER 触发的时机
   before：目标语句激活前执行触发器
   after：目标语句激活后执行触发器 b.
INSERT|UPDATE|DELETE 事件类型
   INSERT：表新增数据时激活 - 用 NEW 关键字代替新数据取值
   UPDATE：表数据修改时激活 - 用 NEW 关键字代替新数据取值、OLD代替旧数据
   DELETE：表数据删除时激活

# 语法格式：
# CREATE TRIGGER <触发器名> <BEFORE|AFTER> <INSERT|UPDATE|DELETE> ON <表名> FOR EACH ROW <主体内容>
CREATE TRIGGER 表名_触发时机_事件类型 BEFORE INSERT ON 表名 FOR EACH ROW /* 主体内容 */ INSERT INTO 表名_BAK (字段名, 字段2) VALUES (NEW.字段名, NEW.字段2);

5、内置函数

5.1、FIND_IN_SET

FIND_IN_SET：用处同【IN】基本相同，都是查询指定值是否在目标值中存在。不同的是【IN】是在数组中匹配数组，而【FIND_IN_SET】是在单个被【,】分隔的字符串中匹配，使用方式为【FIND_IN_SET(验证是否存在的值,【,】分隔的字符串)】

---

FIND_IN_SET 是有返回值的。返回值内容为 以【,】分割字符串形成数组后，指定值在数组中第一次出现的位置（从1开始、返回0则不存在）。例如：FIND_IN_SET(‘2’, ‘1,2,3,4,5’) 返回值是 2。

# 语法格式：
SELECT * FROM 表名 where FIND_IN_SET(字段, '1,2,3,4,5');
# 示例：
SELECT FIND_IN_SET('2', '1,2,3,4,5') FROM dual;

5.2、GROUP_CONCAT

GROUP_CONCAT：MySql列转行函数，数据使用英文逗号[,]隔开

# 语法格式：
SELECT GROUP_CONCAT(待转字段 [ORDER BY 排序字段]) FROM 表名;

5.3、LPAD

lpad：左侧不足补齐

# 语法格式：
SELECT LPAD(待补齐字段, 长度, 补齐内容) FROM 表名;

5.3、RPAD

rpad：右侧不足补齐

# 语法格式：
SELECT RPAD(待补齐字段, 长度, 补齐内容) FROM 表名;

5.4、LENGTH

LENGTH：返回字符串长度

# 示例：
SELECT LENGTH(123456789) '长度' FROM dual;

5.5、REPLACE

REPLACE：替换目标字符串

# 示例：
SELECT REPLACE('aaabbbbbaaaaa', 'b', 'a') '替换后的字符' FROM dual;

5.6、REGEXP

REGEXP：正则匹配目标字符串
正则语法说明：
    (.+)：贪婪匹配、匹配一个或多个【除\r、\n】任意字符

# 示例：
select *
from (select '{"startDate": {"day": 20, "year": 2023, "month": 3}}' dateStr
      union all
      select '{"date": {"day": 20, "year": 2023, "month": 3}, "time": {"hour": 0, "nano": 0, "minute": 0, "second": 0}}' dateStr) t
where dateStr REGEXP '(.+)"startDate"[:] [{]"day": (.+), "year": (.+), "month"[:] (.+)[}](.+)';

5.7、REGEXP_REPLACE

REGEXP_REPLACE：正则替换目标字符串
正则语法说明：
    (.+)：贪婪匹配、匹配一个或多个【除\r、\n】任意字符
    $n：取出贪婪匹配的第n个内容

# 语法格式：
select REGEXP_REPLACE('{"目标字符串', '正则匹配规则', '用于替换的内容');
# 示例：
select REGEXP_REPLACE('{"startDate": {"day": 20, "year": 2023, "month": 3}}', '(.+)"startDate"[:] [{]"day": (.+), "year": (.+), "month"[:] (.+)[}](.+)',
    '$1"startDate": {"date": {"day": $2, "year": $3, "month": $4}, "time": {"hour": 0, "nano": 0, "minute": 0, "second": 0}}$5');

5.8、LAG

LAG：偏移函数-访问当前行前 N 行的值

# 语法格式：
LAG(列名【必填】, 指定向前查看的行数，默认为 1, 默认值) OVER (
    [PARTITION BY 分区列-类似（group by）, ...]
    [ORDER BY order_column, ...]
    [ROWS BETWEEN start AND end]
)

# 示例：
select *, LAG(num) over (order by num)
from (select 1 num
      union all
      select 2 num
      union all
      select 3 num) a;

5.9、WITH AS

WITH AS ：将查询的一部分结果作为一个命名的临时表使用

# 语法格式：
WITH 自定义名称 (column1, column2, ...)
AS (
    查询语句
)
SELECT ... FROM ... WHERE ...

# 示例：
WITH t1 (num)
AS (
    select 1 num
    union all
    select 2 num
)
SELECT * FROM t1;

6、高级查询操作

6.1、生成虚拟序号

mysq中可通过定义变量实现虚拟序号操作

# 语法格式：
select @row_num:=@row_num+1 rowNum from (select @row_num:=0) r;
# 示例
select @row_num:=@row_num+1 rowNum, t.* from ts_user t, (select @row_num:=0) r;