MySQL不存在则创建数据库数据表

来源：互联网发布：如何查看知乎提问者编辑：程序博客网时间：2024/04/30 12:32

创建数据库：

Create Database If Not Exists MyDB Character Set UTF8

创建数据表：

Create Table If Not Exists MyDB.MyTable(

ID Bigint(8) unsigned Primary key Auto_Increment,

thTime DateTime,

name VarChar(128)

)Engine MyISAM

例子：

DROP PROCEDURE if exists pro_add_pointtable;DELIMITER ///*存储过程： pro_add_pointtable操作表：pointtable 参数：pointtable.**/CREATE PROCEDURE pro_add_pointtable (    IN pt_pointid    int,    IN pt_pointname  varchar(50),    /*IN pt_phtable    varchar(50),*/    IN pt_sqtname    varchar(50),    IN pt_value      varchar(50),    IN pt_datetime   datetime,    IN pt_date       char(12),    IN pt_time       char(15))BEGIN    DECLARE pt_phtable VARCHAR(20);    DECLARE tablestr VARCHAR(500);    DECLARE insertpoint VARCHAR(500);    SET pt_phtable = CONCAT('point_',pt_pointid);    INSERT INTO pointtable(pointid,pointname,phtable,sqtname,value,datetime,date,time)    VALUES(pt_pointid,pt_pointname,pt_phtable,pt_sqtname,pt_value,pt_datetime,pt_date,pt_time);    SET tablestr = CONCAT('CREATE TABLE IF NOT EXISTS ',pt_phtable,'(                       id int(11) not null auto_increment,                       sqtname varchar(50),                       value varchar(50),                       datetime datetime,                       date char(12),                       time char(15),                       primary key (id)                    )                    ENGINE = InnoDB                    DEFAULT CHARACTER SET = utf8                    COLLATE = utf8_bin;');    SET @v_sql = tablestr;    PREPARE createpointTabale FROM @v_sql;    EXECUTE createpointTabale;    DEALLOCATE prepare createpointTabale;      SET insertpoint = CONCAT('insert into ',pt_phtable,'(sqtname,value,datetime,date,time) values(''',        pt_sqtname,''',''',pt_value,''',''',pt_datetime,''',''',pt_date,''',''',pt_time,''')');    SET @i_sql = insertpoint;    PREPARE insertpointTabale FROM @i_sql;    EXECUTE insertpointTabale;    DEALLOCATE prepare insertpointTabale; END//DELIMITER ;

0 0