How to use a variable when creating a table in mySQL?

April 18, 2022

I’m trying to declare an ENUM and use it in table creation. Here is my code:

SET @myEnum= ENUM('fulfilled', 'noshow', 'cancelled');
CREATE TABLE `Appointments` (
    `AppointmentID` VARCHAR(36) NOT NULL UNIQUE,
    `AppointmentFulfilled` @myEnum
);

But MariaDB is giving me error: ‘@myEnum is not valid at this position, expecting BIGINT…’

Any ideas?

>Solution :

SQL syntax and identifiers must be fixed at the time the CREATE TABLE statement is parsed. Therefore you cannot use variables or parameters.

You can create a string, interpolate the variable, and then PREPARE and EXECUTE that string as dynamic SQL.

SET @myEnum= 'ENUM(''fulfilled'', ''noshow'', ''cancelled'')';

SET @ddl = CONCAT(
  'CREATE TABLE `Appointments` (
    `AppointmentID` VARCHAR(36) NOT NULL UNIQUE,
    `AppointmentFulfilled` ', @myEnum,
  ')'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;