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 版本相关，所以建议在使用时查阅官方文档以获取最准确和最新的信息。

12. 结束进程

当写的sql语句执行时间过长导致相关的表一直处于锁定状态无法查询，需要结束进程重新优化sql语句

# 查询进程列表，找到执行时间过长的进程
show processlist;
# 杀死进程id
kill 123;

13. 清空数据表

# delete 不带where参数可以删除表中所有内容，带where参数可以删除指定条件的数据，有日志，可以恢复数据
delete from tableName;
# truncate 清空表中所有内容，比delete快，无日志，不可恢复
truncate table tableName;

14.MySQL报错：sql_mode=only_full_group_by

在MySQL 5.7后，MySQL默认开启了SQL_MODE严格模式，对数据进行严格校验。如果代码中含有group by聚合操作，那么select中的列，除了使用聚合函数之外的，如max()、min()等，都必须出现在group by中。

方法1：临时生效

select @@global.sql_mode;
# 如果里面包含 ONLY_FULL_GROUP_BY，去掉之后重新赋值
#示例 返回 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION，去掉之后赋值STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

但是，当我们再一次重新启动数据库时，可能会恢复原样，还是会出现ONLY_FULL_GROUP_BY的报错，这就需要我们再一次修改数据库配置。

方法2：永久生效
修改配置文件 my.ini
在[mysqld]模块下新增一行配置：（建议把方法一中查询到的配置去掉ONLY_FULL_GROUP_BY后配置在文件中）

sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

重启mysql即可生效

15.修改数据库名称

操作危险，请在执行操作前，备份数据库
使用shell脚本重命名所有的表
代码如下:

#!/bin/bash

mysqlconn=”mysql -u xxxx -pxxxx -S /var/lib/mysql/mysql.sock -h localhost”
olddb=”db_name”
newdb=”new_db_name”

#$mysqlconn -e “CREATE DATABASE $newdb”
params=$($mysqlconn -N -e “SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='$olddb'”)

for name in $params; do
$mysqlconn -e “RENAME TABLE $olddb.$name to $newdb.$name”;
done;

#$mysqlconn -e “DROP DATABASE $olddb”

16.mysql统一表字段的编码方式

ALTER TABLE mytable CHARACTER SET = utf8mb4, COLLATE = utf8mb4_unicode_ci;

17.MySQL截取特定字符后的数据

SUBSTRING_INDEX函数用于截取指定分隔符之前或之后的一部分字符串。其语法如下：

SUBSTRING_INDEX(str, delimiter, count);

str: 要截取的字符串
delimiter: 分隔符
count: 截取的次数，取正数表示从开头截取，取负数表示从末尾截取

SELECT SUBSTRING_INDEX('www.example.com', '.', 2) AS result;
+----------------+
| result         |
+----------------+
| www.example    |
+----------------+