Creating MySQL Variable Table Naming

Question

I am creating a database for a collection of servers for minecraft, and as I configured it, I want the table to be created for each server as it is added. At the moment, everything works for me, except that I can’t get the tables that are created to contain the IP address. I want the table to be something like [IP] _Players, where [IP] is replaced with the actual IP address that will be sent through the function through which it is created. Here is what I still have:

DELIMITER $$ CREATE PROCEDURE `minecraft`.`AddServer` (ip Text) BEGIN DECLARE play TEXT; DECLARE tran TEXT; SET play = ip + '_Players'; SET tran = ip + '_Transactions'; INSERT INTO `minecraft`.`Server_Data` (`Server_IP`) VALUES (ip); CREATE TABLE `minecraft`.play ( `Player` TEXT NOT NULL , `Balance` DOUBLE NOT NULL DEFAULT 100 , `Warnings` INT NOT NULL DEFAULT 0 , `Offences` INT NOT NULL DEFAULT 0 , UNIQUE INDEX `Player_UNIQUE` (`Player` ASC) ); CREATE TABLE `minecraft`.tran ( `Time` TIMESTAMP NOT NULL , `Player` TEXT NOT NULL , `Destination` TEXT NOT NULL , `Amount` DOUBLE NOT NULL , `Description` TEXT NOT NULL , PRIMARY KEY (`Time`) ); END 

Instead of creating it as 192.168.001.107_Players when CALL minecraft.AddServer('192.168.001.107'); preformed, it creates a table called play . What am I doing wrong?

Answer 1

I played and worked on it. Please note that you cannot have periods in the table name. Thus, you can use the REPLACE function to replace periods with underscores, for example.

 DELIMITER $$ CREATE PROCEDURE `minecraft`.`AddServer` (ip Text) BEGIN DECLARE play varchar(500); DECLARE STMT varchar(500); SET play = CONCAT(ip, '_Players'); SET @sql_stmt = CONCAT('CREATE TABLE minecraft.', play, ' ( `Player` VARCHAR(50) NOT NULL , `Balance` DOUBLE NOT NULL DEFAULT 100 , `Warnings` INT NOT NULL DEFAULT 0 , `Offences` INT NOT NULL DEFAULT 0 , UNIQUE INDEX `Player_UNIQUE` (`Player` ASC) );'); PREPARE STMT FROM @sql_stmt; EXECUTE STMT; END$$ Delimiter ; 

Answer 2

You should use prepared statements, I think

Prepared SQL syntax statements .