mysql create table dynamically

by

To make a string represent a table (or database) name you will need to concat your query string with the variable and prepare/execute a statement right in the stored procedure. Here’s a basic example.

-- DROP PROCEDURE IF EXISTS createLogTable;
DELIMITER //
CREATE PROCEDURE createLogTable(tblName VARCHAR(255))
BEGIN
    SET @tableName = tblName;
    SET @q = CONCAT('
        CREATE TABLE IF NOT EXISTS `' , @tableName, '` (
            `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
            `something` VARCHAR(10) NOT NULL,
            `somedate` DATETIME NOT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    ');
    PREPARE stmt FROM @q;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    -- and you're done. Table is created.
    -- process it here if you like (INSERT etc)
END //

Then… CALL createLogTable('exampleTable');

So the basic idea is

  1. concat the procedure parameter(s) with your query as necessary
  2. prepare/execute a statement from this query string

More Related Contents:

  1. what will be the sql query code of this “Healthcare” database?
  2. Rank function in MySQL
  3. Find nearest latitude/longitude with an SQL query
  4. How to group by week in MySQL?
  5. Can I use a function for a default value in MySql?
  6. Mysql order by specific ID values
  7. How do I check if a column is empty or null in MySQL?
  8. ERROR 1130 (HY000): Host ” is not allowed to connect to this MySQL server [duplicate]
  9. ON DUPLICATE KEY + AUTO INCREMENT issue mysql
  10. What is this operator in MySQL?
  11. SELECT * WHERE NOT EXISTS
  12. Row number per group in mysql
  13. Resetting ROOT password in MySQL 5.6
  14. How to fuzzy match email or telephone by Elasticsearch?
  15. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  16. Field ‘id’ doesn’t have a default value?
  17. “SELECT COUNT(*)” is slow, even with where clause
  18. is there a mysql function to decode html entities?
  19. MySQL fails on: mysql “ERROR 1524 (HY000): Plugin ‘auth_socket’ is not loaded”
  20. MySQL – SQL_BIG_SELECTS
  21. Check if a column exists in a table with SQL
  22. How to use unsigned int / long types with Entity Framework?
  23. How to Prevent Connection Timeouts for Large MySQL Imports
  24. Can I launch a trigger on select statement in mysql?
  25. How do you write a case insensitive query for both MySQL and Postgres?
  26. How to use MySQL DECIMAL?
  27. MySql. How to use Self Join
  28. SQL doesnt differentiate u and ü although collation is utf8mb4_unicode_ci
  29. apache mysql – “packets out of order” on 3306
  30. Disadvantages of quoting integers in a Mysql query?

Leave a Comment