Comments - CREATE TABLE

11 years, 2 months ago Miro Wikgren
This comment has the status of 'removed' and can only be seen by you.

"[DEFAULT] CHARACTER SET (or [DEFAULT] CHARSET) is used to set a default character set for the table. This is the character set used for all columns where an explicit character set is not specified."

That's a bit misleading, since it doesn't seem possible to set the character set for a single column in MariaDB...

 
11 years, 2 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)
 
11 years, 2 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.

 
11 years, 2 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)

 
11 years, 2 months ago Miro Wikgren

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

 
11 years, 2 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.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.