mysql命令记录

记录一些用到的命令

1.自增id重置 从0开始增长

ALTER TABLE 表名 AUTO_INCREMENT=1;

2.group by 报错处理

sql语句中使用了group by 时报错信息含有：this is incompatible with sql_mode=only_full_group_by
查看sql_mode会发现有only_full_group_by，把它去掉就可以了

select @@GLOBAL.sql_mode;
SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
//上面是设置整个数据库的，对于已存在的数据库需要进到数据库里面执行命令
SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

查到有些文章讲这是暂时性的，重启之后问题还会复现
但是不清楚是不是因为我是docker镜像启动的mysql，执行完上述语句后进到数据库里执行带有group by的语句是成功的，但代码连接到数据库执行还是会出现这个报错，在重启docker之后才执行成功
再有就是一劳永逸的方法，直接改mysql的配置文件
Linux下文件地址一般在：/etc/my.cnf，/etc/mysql/my.cnf

找到sql-mode的位置，去掉ONLY_FULL_GROUP_BY
然后重启MySQL；
有的my.cnf中可能没有sql-mode，需要追加：

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3.Incorrect table definition; there can be only one auto column and it must be defined as a key 自增主键报错处理

自增的属性列必须是主键，想要设置一个自增字段，那么这个字段必须是主键，且一个表只能设置一个自增列，否则会报错：Incorrect table definition; there can be only one auto column and it must be defined as a key（一个表只能有一个自增列并且该列必须是主键。）

4.分组排序取每组第一条

select * from (select distinct(id) as aid, param1 from tableA order by orderParam desc) a group by a.groupParam;

先排序后分组
distinct(id) 的作用：防止排序失效

5.查询一张表在另一张表不存在的数据

有A和B两张表，通过字段A进行关联，由于长时间未清理导致数据错乱，需要找出表A和表B中在另一张表中不存在的数据并删除

-- 找出表A中存在但表B中不存在的数据
select * from tableA
where columnA not in (select columnA from tableB);

6.mysql 加减时间函数 (毫秒、秒、分、时、天、周、月、季、年)

-- 时间加法
adddate();
select adddate(now(),1) //加1天
select adddate(now(), interval 1 microsecond) //加1毫秒
select adddate(now(), interval 1 second) //加1秒
select adddate(now(), interval 1 minute) //加1分钟
select adddate(now(), interval 1 hour) //加1小时
select adddate(now(), interval 1 day) //加1天
select adddate(now(), interval 1 week) //加1周
select adddate(now(), interval 1 month) //加1月
select adddate(now(), interval 1 quarter) //加1季
select adddate(now(), interval 1 year) //加1年

-- 时间减法
subdate();
select subdate(now(),1) //减1天
select subdate(now(), interval 1 microsecond) //减1毫秒
select subdate(now(), interval 1 second) //减1秒
select subdate(now(), interval 1 minute) //减1分钟
select subdate(now(), interval 1 hour) //减1小时
select subdate(now(), interval 1 day) //减1天
select subdate(now(), interval 1 week) //减1周
select subdate(now(), interval 1 month) //减1月
select subdate(now(), interval 1 quarter) //减1季
select subdate(now(), interval 1 year) //减1年

7.MySQL数据库查询字符串数据格式之不足自动填充“0”字符以达到指定位数

LPAD(str,len,padstr);
-- 返回字符串str，左填充用字符串padstr填补到len字符长度。 如果str为大于len长，返回值被缩短至len个字符(即，不能超过 len 长)。
-- LPAD(字段名, 长度, “填充字符”) : 左填充 0001, 0002。
SELECT LPAD('1',4,'0');
'0001'
RPAD(str,len,padstr);
-- 返回字符串str，右补齐字符串 padstr 填补到len字符长度。如果 str 的长度大于len，返回值被缩短至 len 个字符。
-- RPAD(字段名, 长度, “填充字符”) : 右填充 1000, 2000。
SELECT RPAD('1',4,'0');
'1000'

8.日期时间循环

CREATE PROCEDURE test()
BEGIN
DECLARE time DATETIME DEFAULT '2024-04-15 09:00:00';
DECLARE i INT DEFAULT 1;
  WHILE i <= 10
    DO
      -- 其他sql语句
      SET i = i + 1;
      SET time = DATE_ADD(time,INTERVAL 20 second); //加20s
      -- 这里可以参考上面6的时间参数调整为年月日
    end WHILE;
END;
CALL test();

9.格式化时间为日期

DATE_FORMAT(字段或参数,'%Y-%m-%d');

10.字符串拼接

-- CONCAT函数
-- CONCAT(char c1, char c2, …, char cn);
CONCAT(param1, 'a', …, paramN);
-- "+"操作符
-- “+”适合整数形式的字符串相加，MySQL会尝试将加号两端的字段值尝试转换为数字类型，如果转换失败则认为字段值为0。
select '1'+'2'; --输出结果为3
select 'a'+'b'; --输出结果为0
select  '1'+'2'+'a'; --输出结果为3
-- 假如其中一个字段为NULL，则用空字符串代替NULL，否则会输出null，如果需要间隔可以用空格
select concat('a',null,'b'); --输出结果为null
select concat('a','','b'); --输出结果为ab
select '1'+null; --输出结果为null
select '1'+''; --输出结果为1

11.全文搜索关键字

在 MySQL 中，MATCH 是用于全文搜索的关键字。在使用 MATCH 进行全文搜索时，需要注意以下几点：

1. MATCH 关键字只能与 FULLTEXT 索引一起使用。所以，在进行全文搜索之前，请确保你的表上已经创建了 FULLTEXT 索引。（在实践中还需要使用解析器）

2. MATCH 关键字可以与 AGAINST 函数一起使用，用于指定搜索的关键词。例如，可以这样使用 MATCH 和 AGAINST：

SELECT * FROM tableName
WHERE MATCH(columnName) AGAINST('keyword');

3. MATCH 的搜索结果是按照相关性排序的，默认情况下，相关性最高的结果排在最前面。

4. MATCH 的搜索结果也可以设置返回的数量限制。可以使用 LIMIT 语句来限制返回的结果数量

需要注意的是，全文搜索功能的具体使用方式可能还与你所使用的 MySQL 版本相关，所以建议在使用时查阅官方文档以获取最准确和最新的信息。