5 years, 3 months ago Elena Stepanova

Hi,

Could you please elaborate on why it is not possible to set character set for a column in MariaDB? From the syntax point of view, there should be no problem (see below); if you encountered that it does not work, please submit a report at our bugtracking system.

MariaDB [test]> create table t1 ( 
a varchar(32) charset utf8, 
b varchar(16) 
) engine=MyISAM 
default charset latin1;
Query OK, 0 rows affected (0.10 sec)

MariaDB [test]> select column_name, character_set_name 
from information_schema.columns 
where table_name = 't1';
+-------------+--------------------+
| column_name | character_set_name |
+-------------+--------------------+
| a           | utf8               |
| b           | latin1             |
+-------------+--------------------+
2 rows in set (0.01 sec)
 
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.