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

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?

CodePudding user response：

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;