Set table names using user-defined variables in MySQL stored procedures

Question

Hello,

I want to create a table with a name taking from a user-defined variable. The following code doesn't seem to work (I get a MYSQL syntax error near CREATE TABLE line)

Thanks in advance for the help

SET @ratingsNewTableName = CONCAT('ratings_', 'anotherString');

CREATE TABLE IF NOT EXISTS @ratingsNewTableName (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `user_id` INT(11) NOT NULL,
   `movie_id` INT(11) NOT NULL,
   `rate` DOUBLE NOT NULL,
   `date` DATE NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Answer 1

I want to create a table with a name taking from a user-defined variable

Not an answer to your question, but I think this is a bad idea. It invites heaps of trouble.

• You'll have to deal with special characters and encodings in the table name

• You'll have to filter out characters forbidden in a table name

• The range of usable characters will depend on the abilities of the underlying file system, because mySQL creates its data files using the table name

• You may run into long-term trouble because mySQL treats tables differently on different systems. Case sensitivity is dictated by the underlying file system (Table names are case sensitive on Linux/Unix, insensitive on Windows)

I wouldn't do this. I'd much rather have one huge table, and a smaller user table. I'd keep the user specified variable in the user table, and have all records for all users in the huge table, told apart by a "user" key.

Answer 2

SET @sql := CONCAT('CREATE TABLE IF NOT EXISTS ', @tableName,'( id INT(11) NOT NULL AUTO_INCREMENT, user_id INT(11) NOT NULL, movie_id INT(11) NOT NULL, rate DOUBLE NOT NULL, date DATE NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1' ); 
PREPARE mySt FROM @sql; 
EXECUTE mySt;