Mysql之存储过程,动态创建表和数据库-,动态删除-yellowcong

今天在弄mycat 的时候,有一个需求,就是创建12个数据库,然后数据库里面都创建一个表,我当时想象,这个玩意也不难,于是寻思通过存储过程来动态创建表,只需要传递进来数据的前缀名称和表名称,即可创建12个表,每个库里面都有一个固定格式的表。存储过程中,不能有tab制表符,不然就报错

存储过程

存储过程的动态建表和sql,需要依赖于PREPARE 和EXECUTE 函数,通过动态生成表的方式,大大的加速了表的创建和开发流程

-- 删除存储过程
DROP PROCEDURE IF EXISTS CREATE_USER_DB;

-- 添加; 的转义
DELIMITER ;;

-- 创建存储过程
CREATE PROCEDURE CREATE_USER_DB(IN dbPre VARCHAR(32) CHARSET utf8,IN tableNm VARCHAR(32) CHARSET utf8)
BEGIN
    -- 声明变量
    DECLARE i INT DEFAULT 0;
    DECLARE dbname VARCHAR(32) DEFAULT '';
    -- 声明 表名称
    DECLARE tblname VARCHAR(32);

    SET i = 1;
        WHILE i <= 12 DO 

            SET dbname = CONCAT(dbPre,i);

            SET tblname = CONCAT(dbname,'.',tableNm);

            -- 创建数据库    
            SET @createDatabasesql = CONCAT('CREATE DATABASE IF NOT EXISTS ',dbname);

            -- 执行动态生成的sql语句
            PREPARE temp FROM @createDatabasesql;
            EXECUTE temp;

            -- 删除表
            SET @delTabl = CONCAT(' DROP TABLE IF EXISTS ',tblname);

            -- 执行动态生成的sql语句
            PREPARE temp FROM @delTabl;
            EXECUTE temp;

            -- 创建表
            SET @createTbsql = CONCAT('create table ',tblname,'(addData datetime,dbname varchar(32) ,username varchar(32) ,age int(3))');

            -- 执行动态生成的sql语句
            PREPARE temp FROM @createTbsql;

            EXECUTE temp;

            -- 增加角标
            SET i = i + 1; 
    END WHILE;
END;
;;
DELIMITER ;

建立存储过程
这里写图片描述

调用存储过程

-- db_node 这个是数据库名称
-- user 是表的名称
CALL CREATE_USER_DB('db_node','user');

这里写图片描述

动态删除

-- 删除存储过程
DROP PROCEDURE IF EXISTS DELETE_USER_DB;

-- 添加; 的转义
DELIMITER ;;

-- 创建存储过程
CREATE PROCEDURE DELETE_USER_DB(IN dbPre VARCHAR(32) CHARSET utf8)
BEGIN
    -- 声明变量
    DECLARE i INT DEFAULT 0;
    DECLARE dbname VARCHAR(32) DEFAULT '';
    -- 声明 表名称
    DECLARE tblname VARCHAR(32);

    SET i = 1;
        WHILE i <= 12 DO 

            SET dbname = CONCAT(dbPre,i);

            -- 创建数据库    
            SET @createDatabasesql = CONCAT('DROP DATABASE IF EXISTS ',dbname);

              -- huoqu
              SELECT @createDatabasesql;

            -- 执行动态生成的sql语句
            PREPARE temp FROM @createDatabasesql;
            EXECUTE temp;

            -- 增加角标
            SET i = i + 1; 
    END WHILE;
END;
;;
DELIMITER ;

调用

-- 删除数据库
CALL DELETE_USER_DB('db_node');

这里写图片描述

问题集合

Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_g eneral_ci,COERCIBLE) for operation ‘concat’

出现这个问题的原因,大致是编码导致的,解决办法是
这里写图片描述

-- 查看编码
show variables like 'character%';

#编辑mysql的配置文件
vim /etc/my.cnf

#在[mysqld] 下面添加utf8配置
character_set_server = utf8
collation-server=utf8_general_ci


#重启服务
service mysql restart

这里写图片描述

配置my.cnf文件
这里写图片描述

修改成功后
这里写图片描述

mysql: unknown variable ‘default_charater_set=utf8’

这个问题是由于高级版本的mysql不识别default_charater_set=utf8,需要修改为

character_set_server = utf8

即可。那mysqld:unknown variable ‘default-character-set=utf8’的错误原因是什么呢?因为参数:default-character-set=utf8 在较新版本的MySQL 中已移除。所以,建议高版本的MySQL使用”character-set-server“,而不要使用“default-character- set”。

这里写图片描述

第二种修改mysql编码的方法

set character_set_database =utf8;
set character_set_results =utf8;
set character_set_server =utf8;
set character_set_system =binary;
SET collation_server = utf8_general_ci;
SET collation_database = utf8_general_ci;
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页