MariaDB字符集编码，mariadb字符集
原文链接：http://blog.sina.com.cn/s/blog_8ea8e9d50102vv1z.html
查看数据库支持的所有字符集 SHOW CHARACTER SET; SHOW COLLATION;
一.查看编码
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.41-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
默认是latin1,外部访问数据乱码的问题就出在这个connection连接层上
二.设置编码 命令行修改
1. SET NAMES 'utf8'; 它相当于下面的三句指令： SET character_set_client = utf8; SET character_set_results = utf8; SET character_set_connection = utf8; 一般只有在访问之前执行这个代码就解决问题了
2. 创建数据库是指定编码 CREATE DATABASE database_name CHARACTER SET utf8;
MariaDB [(none)]> CREATE DATABASE foo CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE foo;
Database changed
MariaDB [foo]> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
MariaDB [foo]> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
注意：虽然在建库的时候有指定字符集，但character_set_server 仍然是latin1, 需要修改配置文件/etc/my.cnf3. 创建表指定编码 CREATE TABLE `type` ( `id` int(10) unsigned NOT NULL auto_increment, `flag_deleted` enum('Y','N') character set utf8 NOT NULL default 'N', `flag_type` int(5) NOT NULL default '0', `type_name` varchar(50) character set utf8 NOT NULL default '', PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8;
示例：
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
CREATE OR REPLACE TABLE mytable
(columnn1 INT,
columnn2 VARCHAR(10),
column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin1_general_cs;
还可以限制某个字段的字符集作为筛选条件4. 修改数据库成utf8
ALTER DATABASE database_name CHARACTER SET utf8;
5. 修改表默认用utf8
ALTER TABLE table_name CHARACTER SET utf8;
6. 修改字段用utf8
MariaDB [foo]> DESC hello;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(50) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [foo]> ALTER TABLE hello MODIFY name char(50) CHARACTER SET utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
配置文件 Linux下一般是 /etc/my.cnf，Windows下一般在系统目录下或者在MySQL的安装目录下名字叫my.ini
[mysqld] character_set_server = utf8 lower_case_table_names = 1 //表名不区分大小写（此与编码无关）
[mysqld_safe] character_set_server = utf8
[mysql] default-character-set = utf8
[mysql.server] default-character-set = utf8
[client] default-character-set = utf8
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.41-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
init_connect = 'SET collation_connection = utf8_general_ci'
init_connect = 'SET NAMES utf8'
character_set_server = utf8
collation_server = utf8_general_ci
[mysqld_safe]init_connect = 'SET collation_connection = utf8_general_ci'
init_connect = 'SET NAMES utf8'
character_set_server = utf8
collation_server = utf8_general_ci