MySQL代码片段

MySQL使用游标批量更新

注意：要先创建存储过程，再调用。如果直接在查询中使用代码会报错。

DROP PROCEDURE IF EXISTS SetInnerCode;
CREATE PROCEDURE SetInnerCode()
BEGIN
​
DECLARE v_id INT DEFAULT 0;
DECLARE v_inner_code INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
​
DECLARE v_cur CURSOR FOR SELECT id FROM t_info_student WHERE team_id=30;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN v_cur;
FETCH v_cur INTO v_id;
WHILE(NOT done) DO
set v_inner_code = v_inner_code + 1;
UPDATE t_info_student SET inner_code = CAST(v_inner_code AS CHAR) WHERE id = v_id;
FETCH v_cur INTO v_id;
END WHILE;
CLOSE v_cur;
​
END;
​
CALL SetInnerCode();

 

mysql查询和修改指定数据库中所有表中包含的某个字段

SELECT 
　　TABLE_NAME '表名', 
　　COLUMN_NAME '字段' 
FROM 
　　information_schema.COLUMNS 
WHERE 
　　TABLE_SCHEMA = 'school' 
AND COLUMN_NAME = 'school_id' 

TABLE_NAME ：表名

COLUMN_NAME ：列名（字段）

information_schema：mysql自带库，里面都是mysql的元数据，元数据指的是数据的数据，有点绕，该数据库里面存的就是数据库的名称、列的数据类型、访问权限等

TABLE_SCHEMA ：指定的数据库

COLUMN_NAME ：指定的参数名

 

使用存储过程批量修改数据库中某个字段的值

DROP PROCEDURE IF EXISTS schoolId;
CREATE PROCEDURE schoolId(IN oldId CHAR(15),IN newId CHAR(15))
BEGIN
-- 定义循环条件
DECLARE flag INT DEFAULT 0; 
-- 保存表名
DECLARE tname VARCHAR(50);
-- 查询数据库test中含有userId列的表,如果区分大小写使用binary COLUMN_NAME = 'userId'
DECLARE result CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_SCHEMA = 'school' AND COLUMN_NAME = 'school_id';
-- 退出循环
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET flag = 1; 
-- 打开游标
OPEN result;
WHILE flag <> 1 DO
-- 游标指向下一个位置,可以有多个数据,比如FETCH result INTO tname,ttype,...;
FETCH result INTO tname;
-- 拼接字符串表名sql,根据需要使用CONCAT函数连接
-- SET @execSql = CONCAT('SELECT * FROM ',tname,' WHERE imei = ',oldImei,' ;'); 
SET @execSql = CONCAT('UPDATE ', tname, ' SET school_id = ',newId,' WHERE school_id = ',oldId,' ;'); 
PREPARE stmt FROM @execSql;
EXECUTE stmt;
END WHILE;
END;
-- 调用存储过程更新数据
CALL schoolId('7','4');

 

禁用（开启）外键约束

--开启
SET FOREIGN_KEY_CHECKS=1;
​
--禁用
SET FOREIGN_KEY_CHECKS=0;

 

获取一段时间的日期列表（查询日期列表、本月所有日期）

SELECT
    DATE_FORMAT(
        ADDDATE(
            '2017-06-01',
            INTERVAL @d DAY
        ),
        '%Y-%m-%d'
    ) AS date ,@d :=@d + 1 day
FROM 表名,
(SELECT @d := 0) temp
WHERE
    ADDDATE(
        '2017-06-01',
        INTERVAL @d DAY
    ) <= DATE_FORMAT('2017-06-05', '%Y-%m-%d')
ORDER BY 
    day;