5 years, 3 months ago Miro Wikgren

The problem seems to only occur when also specifying a default value; CHARSET must be specified before any DEFAULT value:

MariaDB [data]> CREATE TABLE foo2 (id varchar(120) DEFAULT 'bar' CHARSET utf8);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHARSET utf8)' at line 1

Not quite MySQL compatible.

 
5 years, 3 months ago Elena Stepanova

In column_definition specified above CHARSET is a part of data_type, this is why it should go before the default value; and it works exactly the same way in MySQL:

mysql> CREATE TABLE foo2 (id varchar(120) DEFAULT 'bar' CHARSET utf8);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
 version for the right syntax to use near 'CHARSET utf8)' at line 1

mysql> CREATE TABLE foo2 (id varchar(120) CHARSET utf8 DEFAULT 'bar');
Query OK, 0 rows affected (0.05 sec)

mysql> select @@version, @@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 5.5.29    | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.00 sec)

 
5 years, 3 months ago Miro Wikgren

My apologies, it does seem to work the same in MySQL.

 
5 years, 3 months ago Federico Razzoli

I've noticed that many SQL beginners lose much time on syntax errors, before understanding that the clauses must always follow exactly the correct order.