Mysql create table in database update#

SELECT CONCAT('ERROR: Table '', tableName, '' does not exist in the schema '', schemaName, ''. IF fn_table_exists(schemaName, tableName)ĬALL statement(CONCAT('CREATE TABLE IF NOT EXISTS ', backupTableName,' LIKE ', tableName)) ĬALL statement(CONCAT('INSERT INTO ', backupTableName,' SELECT * FROM ', tableName)) ĬALL statement(CONCAT('CHECKSUM TABLE ', backupTableName,', ', tableName)) There are two main ways of creating tables in MySQL databases: Executing a query that includes the CREATE TABLE statement Using the corresponding functionality of MySQL-related tools and IDEs The first approach is helpful when you need to create tables specifically via a script.

Mysql create table in database update#

Which will result in a duplicate table called tableName_20181022235959 If called when SELECT NOW() ĬREATE PROCEDURE duplicate_table(IN tableName VARCHAR(255))ĭECLARE schemaName VARCHAR(255) DEFAULT SCHEMA() ĭECLARE today VARCHAR(14) DEFAULT REPLACE(REPLACE(REPLACE(NOW(), '-', ''), ' ', ''), ':', '') - update year 10000ĭECLARE backupTableName VARCHAR(255) DEFAULT CONCAT(tableName, '_', today) SET = concat('RENAME TABLE ',tbl_name,'_NEW TO ',tbl_name) Įxpanding on this answer one could use a stored procedure: CALL duplicate_table('tableName') RENAME TABLE GLS_DEVICES_NEW TO GLS_DEVICES SET = concat('RENAME TABLE ',tbl_name,' TO ',tbl_name,'_OLD') RENAME TABLE GLS_DEVICES TO GLS_DEVICES_OLD SET = concat('CREATE TABLE ',tbl_name,'_NEW LIKE ',tbl_name) CREATE TABLE GLS_DEVICES_NEW LIKE GLS_DEVICES SET = concat('DROP TABLE IF EXISTS ',tbl_name,'_OLD') I suggest also to create a PLSQL procedure as the following example: DELIMITER //ĬREATE PROCEDURE backup_table(tbl_name varchar(255)) This avoids the INSERT AS SELECT statement that, in case of table with a lot of records can take time to be executed.

RENAME TABLE table_name _NEW TO table_name RENAME TABLE table_name TO table_name_OLD My purpose is: DROP TABLE IF EXISTS table_name_OLD ĬREATE TABLE table_name_NEW LIKE table_name In this way, independently from the number of records in the table, you can perform the duplication instantly. The better way to duplicate a table is using only DDL statement.