Mysql小技巧

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 DISTINCT CONCAT(
     'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`',
     ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
   ) AS '同步表结构脚本'
   FROM information_schema.columns
   WHERE TABLE_SCHEMA = 'your_database_name';

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

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

--让外键临时失效
SET FOREIGN_KEY_CHECKS=0;
--让外键生效
SET FOREIGN_KEY_CHECKS=1;
--删除外键（删除table_name的外键column_name_fk_1）
ALTER TABLE table_name DROP FOREIGN KEY column_name_fk_1;
--外键恢复（恢复原字段data_base_name.table_name.column_name的外键‘column_name_fk_1’关联：table_name2.column_name2）
ALTER TABLE `data_base_name`.`table_name` add CONSTRAINT `column_name_fk_1` FOREIGN KEY (`column_name`) REFERENCES `table_name2` (`column_name2`) ON DELETE RESTRICT ON UPDATE RESTRICT;

SELECT
IS_NULLABLE,
	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 ',IFNULL(COLUMN_DEFAULT,'NULL')),' '),' COMMENT \'',COLUMN_COMMENT,'\';' ) AS '修复字段编码脚本' 
FROM information_schema.`COLUMNS` 
where DATA_TYPE='varchar'
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';