MySQL实用技巧汇总-优快云博客

本文链接：https://blog.youkuaiyun.com/JohnGene/article/details/125197367

1、生成随机手机号码

-- 生成随机手机号函数
CREATE  FUNCTION generatePhone() RETURNS char(11) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE head VARCHAR(100) DEFAULT '000,156,136,176';
    DECLARE content CHAR(10) DEFAULT '0123456789';
    DECLARE phone CHAR(11) DEFAULT substring(head, 1+(FLOOR(1 + (RAND() * 3))*4), 3);
        
    DECLARE i int DEFAULT 1;
    DECLARE len int DEFAULT LENGTH(content);
    WHILE i<9 DO
        SET i=i+1;
        SET phone = CONCAT(phone, substring(content, floor(1 + RAND() * len), 1));
    END WHILE;
    
    RETURN phone;
END;

-- 调用函数
select generatePhone();
-- 删除
drop function if exists generatePhone;

2、查询表结构及备注

-- 查询一个表的表结构
SHOW FULL COLUMNS FROM test_table;
-- 或者这么写：
select COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT,table_schema,NUMERIC_PRECISION 长度（整数）,NUMERIC_SCALE 长度（小数）,CHARACTER_MAXIMUM_LENGTH 长度（字符） from information_schema.COLUMNS where TABLE_NAME='test_table';
-- 查询库中所有表的结构
SELECT
	t.TABLE_NAME,
	t.TABLE_COMMENT,
	c.COLUMN_NAME,
	c.COLUMN_TYPE,
	c.COLUMN_COMMENT 
FROM
	information_schema.`COLUMNS` c,
	information_schema.`TABLES` t 
WHERE
	c.TABLE_NAME = t.TABLE_NAME 
	AND t.TABLE_SCHEMA = 'demo_source'; -- demo_source是数据库名字

3、日期格式化

java中对应的时间格式：yyyy-MM-dd HH:mm:ss

在mysql中是'%Y-%m-%d %H:%i:%s'

DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

4、查询敏感数据时替换中间位数为星号

方式1：【推荐】select concat(left(cardno,3),'****',right(cardno,3)) cardno

方式2：【推荐】select insert(cardno,4,12,'****')

方式3：【不推荐】select REPLACE(cardno,SUBSTR(cardno,4,12),'****')

5、将表结构中的字段替换成小写

执行下面语句（表名和数据库名记得替换）得到的结果复制到文本文档里面，然后再复制出来执行即可将表名大写转小写了。这里转小写的方式主要是用lower函数，同理小写转大写的话就使用upper函数

SELECT concat('ALTER TABLE '
           , '表名'
           , ' CHANGE COLUMN '
           , COLUMN_NAME, ' '
           , LOWER(COLUMN_NAME)
           , ' '
           , COLUMN_TYPE
           , ' COMMENT '''
           , COLUMN_COMMENT
           , ''';') AS '修改脚本'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '数据库名'
  and TABLE_NAME = '表名';

6、Mysql5.7针对Group By问题only_full_group_by限制

select DISTINCT
字段1,字段2,字段3,字段4,字段5
from 表名
where
主键 IN (SELECT MAX(主键) FROM 表名 GROUP BY 去重字段名)

7、查询mysql线程及sql执行时间

select * from information_schema.PROCESSLIST 
where info is not null order by TIME desc;

8、mysql分隔字符串成列表

table a 里面有id,names字段需求是要把逗号分隔的names转化成name列表

SELECT
	a.id,
	substring_index( SUBSTRING_INDEX( a.names, ',', hp.help_topic_id + 1 ), ',', - 1 ) name
FROM
	table a
LEFT JOIN mysql.help_topic hp ON hp.help_topic_id <= length( a.names ) - length( REPLACE ( a.names, ',', '' ) )
	where a.id='xxx';

9、mysql同步所有表的字符集和排序规则

查询出库中所有表的操作脚本后执行他们即可，your_database_name改成你的数据库名

select CONCAT(
        'ALTER TABLE ', 
        tb.TABLE_SCHEMA, 
        '.', 
        tb.TABLE_NAME, 
        ' DEFAULT CHARACTER SET ', 
        'utf8mb4', 
        ' COLLATE ', 
        'utf8mb4_general_ci', 
        ' COMMENT=\'', 
        tb.TABLE_COMMENT, 
        '\';'
    ) AS alter_table_sql
from
    information_schema.TABLES as tb
		JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS ccsa ON tb.TABLE_COLLATION = ccsa.COLLATION_NAME
where
    tb.TABLE_SCHEMA = 'your_database_name'
    and tb.TABLE_TYPE = 'BASE TABLE'
    and ccsa.CHARACTER_SET_NAME != 'utf8mb4'
	and tb.TABLE_COLLATION != 'utf8mb4_general_ci';

10、mysql同步所有表中字段的字符集和排序规则

查询出库中所有表的操作脚本后执行他们即可，database_name改成你的数据库名，table_name改为数据库名或者去掉，table_column_name改成字段名或者去掉，如遇外键可以先让其失效后再让其生效，或者删除外键，改完再恢复

-- 1、查询所有外键备份SQL
SELECT 
    CONCAT(
        'ALTER TABLE ', 
        TABLE_NAME, 
        ' ADD CONSTRAINT ', 
        CONSTRAINT_NAME, 
        ' FOREIGN KEY (', 
        COLUMN_NAME, 
        ') REFERENCES ', 
        REFERENCED_TABLE_NAME, 
        '(', 
        REFERENCED_COLUMN_NAME, 
        ');'
    ) AS foreign_key_statement
FROM 
    information_schema.KEY_COLUMN_USAGE
WHERE 
    REFERENCED_TABLE_SCHEMA = 'your_database_name'
    AND REFERENCED_TABLE_NAME IS NOT NULL;
-- 这里会生成很多  ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(id);  相关语句
-- 查询移除所有外键的SQL
SELECT 
    CONCAT(
        'ALTER TABLE ', 
        TABLE_SCHEMA, 
        '.', 
        TABLE_NAME, 
        ' DROP FOREIGN KEY ', 
        CONSTRAINT_NAME, 
        ';'
    ) AS drop_foreign_key_sql
FROM 
    information_schema.TABLE_CONSTRAINTS
WHERE 
    CONSTRAINT_TYPE = 'FOREIGN KEY'
    AND TABLE_SCHEMA = 'your_database_name';
-- 这里会生成很多  ALTER TABLE orders DROP FOREIGN KEY fk_orders_customers;  相关语句
-- 3、执行移除外键SQL
-- 4、修改表的字符集和排序规则
-- 5、执行上述查询的所有外键备份语句

查询修改字符集和排序规则的sql并执行他们：

SELECT
	CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ',IF(IS_NULLABLE='YES','NULL','NOT NULL'), IF(IS_NULLABLE='YES',CONCAT(' DEFAULT ',IF(COLUMN_DEFAULT is null,'NULL','\'\'')),' '),' COMMENT \'',COLUMN_COMMENT,'\';' ) AS '修复字段编码脚本' 
FROM information_schema.`COLUMNS` 
where (DATA_TYPE='varchar' or DATA_TYPE='char' or DATA_TYPE='text' or DATA_TYPE='longtext' or DATA_TYPE='tinytext' or DATA_TYPE='set' or DATA_TYPE='mediumtext' or DATA_TYPE='enum')
AND CHARACTER_SET_NAME = 'utf8mb4' 
AND COLLATION_NAME != 'utf8mb4_general_ci' 
AND TABLE_SCHEMA != 'data_base_name'
AND TABLE_NAME='table_name'
AND COLUMN_NAME='table_column_name';

11、mysql递归遍历树形数据获取本身及其所有子类

SELECT
    t1.id,
    t1.name,
    t1.parent_id,
    @pv := CONCAT_WS(',', t1.id, @pv) AS ancestors
FROM table t1
JOIN (SELECT @pv := 'xxx') tmp
WHERE  (t1.id = @pv OR FIND_IN_SET(t1.parent_id, @pv));

--xxx代表根节点id

12、Mysql在Linux系统中新增包含多行带分号sql的函数

mysql->

DELIMITER $$    --将语句结束符替换成$$
CREATE FUNCTION `xxx`() 
RETURNS int
DETERMINISTIC   -- 在linux中mysql的日志是二进制的，所以需要加上此语句
READS SQL DATA  -- 在linux中mysql的日志是二进制的，所以需要加上此语句
BEGIN
    -- xxxxx方法体-----
END $$

DELIMITER ;      -- 将语句结束符换回;

13、Mysql在Linux系统中导入导出sql

1、导入语句

root# mysql -uroot -p
mysql-> 输入你的密码
mysql-> source /path/to/your/file.sql;

root# mysql -u username -p database_name < /path/to/your/file.sql
mysql-> 输入你的密码

2、导出语句

-- 单个数据库备份
mysqldump -u root -p mydatabase > mydatabase_backup.sql
-- 多个数据库备份
mysqldump -u root -p --databases db1 db2 > databases_backup.sql
-- 所有数据库备份
mysqldump -u root -p --all-databases > all_databases_backup.sql