Search code examples
mysqlmysql-error-1064

MySQL Creating temporary table syntax error

I am getting a syntax error when I run a MySQL statement.

I know backticks can be added to the tablename to make it work but was wondering why it happens in the first place.

Create temporary table 6514202534e1b20f0d6331 (user_id INT (10)) ENGINE=HEAP;

If I put this in Mysql Query Browser it treats the table name as two seperate words - 6514202534e1 and b20f0d6331.

The table name is generated dynamically and I haven't had a problem with this before, so I was wondering why it stopped working all of a sudden.

Solution

  • I think this is because the server (mysql) understands it in this case as 6514202534*e^1 INT.

    Using the ` character:

    CREATE TEMPORARY TABLE `6514202534e1b20f0d6331` (user_id INT (10)) ENGINE=HEAP;

    In this way the MySQL Server understands (explicitly) that the whole phrase is database, table or field not a value or function, etc.

    For example `order` is legal while just order will rise an error for invalid order clause.