MySql【笔记】

1、select into 语法，如果没查询出数据，则不会执行。

2、批量更改MySql的字符集和排序规则（借鉴：https://www.cnblogs.com/-renyu/p/10776020.html）

表字段更改：（注：将数据库【nonelectricdb_hb】排序规则为【utf8mb4_general_ci】全改为【utf8mb4_0900_ai_ci】，把修正的SQL复制出来运行，字段标准就修复了。）

SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',`COLUMN_NAME` '字段',CHARACTER_SET_NAME '原字符集',COLLATION_NAME '原排序规则',CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') '修正SQL'
FROM information_schema.`COLUMNS` 
WHERE COLLATION_NAME RLIKE 'utf8mb4_general_ci' AND TABLE_SCHEMA='nonelectricdb_hb';

表修复：

SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '原排序规则',CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`TABLES`
WHERE TABLE_COLLATION RLIKE 'latin1';

数据库修复：

SELECT SCHEMA_NAME '数据库',DEFAULT_CHARACTER_SET_NAME '原字符集',DEFAULT_COLLATION_NAME '原排序规则',CONCAT('ALTER DATABASE ',SCHEMA_NAME,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`SCHEMATA`
WHERE DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8';

 

3、mysql在sql中定义参数和赋值的方式

(注：在存储过程中，定义参数顶行定义。获取当前季度的起始日期)

DECLARE _startTime VARCHAR(100);
DECLARE _endTime VARCHAR(100);

-- 方式1

SET _startTime=CONCAT(DATE_FORMAT(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM  CURDATE()),1) + INTERVAL QUARTER(CURDATE())*3-3 MONTH),'%Y-%m-'),'01');
SET _endTime=LAST_DAY(MAKEDATE(EXTRACT(YEAR  FROM CURDATE()),1) + INTERVAL QUARTER(CURDATE())*3-1 MONTH);

-- 方式2

SELECT CONCAT(DATE_FORMAT(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM  CURDATE()),1) + INTERVAL QUARTER(CURDATE())*3-3 MONTH),'%Y-%m-'),'01') INTO _startTime;   
SELECT LAST_DAY(MAKEDATE(EXTRACT(YEAR  FROM CURDATE()),1) + INTERVAL QUARTER(CURDATE())*3-1 MONTH) INTO _endTime; 

 

4、mysql 赋值的3种方式

mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。
第一种用法：set @num=1; 或set @num:=1; //这里要使用变量来保存数据，直接使用@num变量
第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……
注意上面两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值”

第三种用法：select 字段名1,字段名2 into @变量1,@变量2 from 表名 where ......

在函数或存储过程或触发器中，在不能使用set的时候推荐第三种，因为第二种会在执行时返回查询结果，这在函数或触发器中会报 “Not allowed to return a result set from a function”错误。而第三种则不会报错。

 

5、数据清理存储

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_del_table`()
BEGIN

DECLARE has_data INT DEFAULT 0; -- 默认值为0，表示没有数据

SELECT COUNT(*) INTO has_data FROM table where createTime<DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY); -- 检查表中的记录数

IF has_data > 0 THEN
   
     -- 1. 复制数据到新表
     SET @SqlStr=CONCAT('create table IF NOT EXISTS table_',date_format(NOW(), '%Y_%m_%d' ),' SELECT * FROM table where createTime<DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY);');

   -- 预处理拼接好的字符串
   PREPARE stmt FROM @SqlStr;
   -- 执行拼接SQL语句
   EXECUTE stmt;
   deallocate prepare stmt;

   -- 2. 删除半年前数据
   DELETE FROM table where createTime<DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY); -- 删除大于180的数据
     
END IF;

END

 

 MySql事件创建和删除

-- 创建事件
CREATE EVENT event_chenze_test
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN

    -- 这里放置需要执行的代码
    SELECT 1;

END;



-- 删除事件
DROP EVENT IF EXISTS event_chenze_test;

 

 

。。。待补充！！！