1

I have the following query to create a table:

  CREATE TABLE qrtz_simprop_triggers
            (          
            SCHED_NAME VARCHAR(120) NOT NULL,
            TRIGGER_NAME VARCHAR(200) NOT NULL,
            TRIGGER_GROUP VARCHAR(200) NOT NULL,
            STR_PROP_1 VARCHAR(512) NULL,
            STR_PROP_2 VARCHAR(512) NULL,
            STR_PROP_3 VARCHAR(512) NULL,
            INT_PROP_1 INT NULL,
            INT_PROP_2 INT NULL,
            LONG_PROP_1 BIGINT NULL,
            LONG_PROP_2 BIGINT NULL,
            DEC_PROP_1 NUMERIC(13,4) NULL,
            DEC_PROP_2 NUMERIC(13,4) NULL,
            BOOL_PROP_1 BOOL NULL,
            BOOL_PROP_2 BOOL NULL,
            PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) ENGINE=InnoDB 
default charset=<value of character_set_client>;

That is, if the value of character_set_client is UTF8, then default charset should be utf8 and if character_set_client is latin1, then default charset should be latin1.

This is very important for me and I am not able to crack it even after spending hours. Any help would be highly appreciated.

Akhil Prajapati
  • 1,221
  • 3
  • 14
  • 23

1 Answers1

1

You'd have to do this with dynamic SQL. You can't use variables directly in a CREATE TABLE statement.

SET @sql = CONCAT('CREATE TABLE ... DEFAULT CHARSET=', @@character_set_client);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

However, I wonder why you're doing this. I would normally allow the table charset to default to the charset for the schema. It seems odd to use the client charset.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks a lot. It was really useful. I was just wondering if we can use a local variable instead of @@character_set_client. Like is it possible if I get the character set of an existing table, store it in a variable and then use it to create the new table? – Akhil Prajapati Jan 18 '19 at 19:04
  • 1
    You would have to use the PREPARE/EXECUTE method I showed with a local variable too. The CREATE TABLE does not accept a string as the charset name, it must use an identifier. – Bill Karwin Jan 18 '19 at 19:09
  • 1
    To get the charset of an existing table, see https://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-mysql-database-table-column-is – Bill Karwin Jan 18 '19 at 19:11
  • Thanks a lot Bill. They all worked together very well. – Akhil Prajapati Jan 20 '19 at 02:26