用MySQL动态创建表从初学者到高手（c mysql动态新建表）

用MySQL动态创建表：从初学者到高手

MySQL是目前世界上最为流行的关系型数据库管理系统，它的灵活性和可扩展性为开发者带来了更多的选择和可能性。在MySQL中，我们可以简单地通过SQL语句进行表的创建和定义，但是对于有字符串拼接、数据类型转换等特殊要求的开发者来说，手动书写SQL语句可能会很麻烦。考虑到这些情况，MySQL也提供了使用动态SQL语句创建表的方法。

初学者如何使用动态SQL语句创建表？

对于初学者来说，动态SQL语句的创建方法可能会稍微有些难度。首先需要明确的是，动态SQL语句是通过字符串拼接来实现的，因此我们需要具备基本的字符串操作能力。

下面我们来看一个最简单的动态创建表的例子：

SET @CreateTableSql = CONCAT('
CREATE TABLE ',
dbName, '.', tableName, ' (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id))'
);
PREPARE createTable FROM @CreateTableSql;
EXECUTE createTable;

在这个例子中，我们首先定义一个变量`@CreateTableSql`，然后通过CONCAT函数将每一个表的信息拼接在一起，最后通过PREPARE和EXECUTE的方式进行创建。对于初学者来说，这个例子的难度不大，但需要注意的是，每一个SQL语句都需要以分号结尾。

中级开发者如何使用动态SQL语句创建表？

对于中级开发者来说，动态SQL语句不再是一个未知的领域。在实际应用中，我们可能需要动态地创建多个表，或者需要根据数据类型、表名等参数来动态地生成SQL语句。

下面我们来看一个稍微复杂一些的例子：

DELIMITER //
CREATE PROCEDURE createTable(
    IN dbName VARCHAR(64),
    IN tableName VARCHAR(64),
    IN columns TEXT CHARSET utf8
     )
BEGIN
    DECLARE sqlString VARCHAR(1024) DEFAULT '';
    DECLARE columnName VARCHAR(128) DEFAULT '';
    DECLARE dataType VARCHAR(64) DEFAULT '';
    DECLARE columnLength INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE maxI INT DEFAULT 0;
    
    SET @columnName = '';
    SET @dataType = '';
    SET @columnLength = 0;
    
    SET SQL_SAFE_UPDATES=0;
    SET @sqlString = CONCAT('CREATE TABLE ',
                            dbName, '.', tableName, ' (');
    SET @columns = REPLACE(@columns, '\r\n', ',');
    SET @columns = REPLACE(@columns, '\n', ',');
    SET @columns = REPLACE(@columns, '\r', ',');
    
    SET @maxI = (LENGTH(@columns) - LENGTH(REPLACE(@columns, ',', '')) + 1);
    
    WHILE i 
        SET @columnName = SUBSTRING_INDEX(@columns, ',', 1);
        SET columns = SUBSTRING(@columns, CHAR_LENGTH(@columnName)+2);
        
        SET @dataType = SUBSTRING_INDEX(@columns, ',', 1);
        IF INSTR(@dataType, '(') > 0 THEN
            SET @columnLength = SUBSTRING_INDEX(@dataType, '(', -1);
            SET @columnLength = SUBSTRING_INDEX(@columnLength, ')', 1);
            SET @dataType = SUBSTRING_INDEX(@dataType, '(', 1);
        END IF;
        SET @sqlString = CONCAT(@sqlString, ' ',
                                @columnName, ' ',
                                @dataType);
        IF @columnLength > 0 THEN
            SET @sqlString = CONCAT(@sqlString, '(', @columnLength, ')');
        END IF;
        IF i 
            SET @sqlString = CONCAT(@sqlString, ',');
        END IF;
        SET i = i + 1;
    END WHILE;
    
    SET @sqlString = CONCAT(@sqlString, ');');
    
    PREPARE stmt FROM @sqlString;
    EXECUTE stmt;
    
    SET SQL_SAFE_UPDATES=1;
    
    END //
DELIMITER ;

在这个例子中，我们定义了一个存储过程`createTable`，它的参数包括数据库名、表名和字段定义。通过字符串操作和循环控制，我们可以在不同的数据类型和字段长度下，自动生成所需的SQL语句，然后通过预处理和执行的方式进行表的创建。

高级开发者如何使用动态SQL语句创建表？

对于高级开发者来说，动态SQL语句基本上已经不再是问题了。在实际应用中，我们可能需要使用到更加复杂的数据类型、SQL语句的优化等。

下面我们来看一个更加复杂的例子：

CREATE PROCEDURE createTestTable()
BEGIN

DECLARE field TEXT CHARSET utf8;
SET field = 'SELECT column_name, data_type, column_type, column_key, column_comment 
            FROM information_schema.columns 
            WHERE table_name = \"teacher\" 
            AND table_schema = (SELECT DATABASE())';

INSERT INTO tables (created, ddl, name) 
VALUES 
    (now(),
     CONCAT('CREATE TABLE IF NOT EXISTS (', 
            SELECT GROUP_CONCAT(
                CONCAT(column_name, ' ', column_type), 
                CONCAT(IF(column_key = 'PRI', ' PRIMARY KEY', ''), 
                    IF(column_comment  '', 
                        CONCAT(' DEFAULT \'', column_comment, '\'', 
                            IF(column_name = 'gender', ' COMMENT \'', '')), ''), 
                    IF(column_name = 'gender', ' CONTACT \'', '')))      
            FROM (
                SELECT column_name, 
                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                        REPLACE(data_type, 'unsigned', ''), 
                        'char', 'VARCHAR'), 
                        'int', 'INT'), 
                        'float', 'FLOAT'), 
                        'timestamp', 'DATETIME'), 
                        'decimal', 'DECIMAL'), 
                        'enum', 'ENUM'), 
                        'text', 'TEXT'
                    ) AS data_type, column_type, column_key, column_comment 
                FROM (', (SELECT field), ') AS t1) AS t2'), 
            ');'), 'teacher');

END 

在这个例子中，我们在存储过程中使用到了SELECT查询，根据查询结果生成了动态SQL语句。同时，我们还进行了数据类型的转换和一些SQL语句的优化，以提高生成的SQL语句的性能和效率。

总结

MySQL动态创建表是一种非常灵活和强大的方式，它为开发者提供了更多的选择和可能性。对于不同开发者，我们可以根据需要使用不同的方法和技术。在实际应用中，我们需要注意SQL注入等安全问题，并尽可能地进行优化和精简，以提高程序的性能和效率。