“MySQL中的character set和collation”的版本间的差异
(创建页面,内容为“==character set和collation的是什么== character set即字符集 我们常看到的UTF-8、GB2312、GB18030都是相互独立的character set。即对Unicode的…”) |
|||
(未显示同一用户的1个中间版本) | |||
第25行: | 第25行: | ||
+--------------------------+---------+-----+---------+----------+---------+ | +--------------------------+---------+-----+---------+----------+---------+ | ||
47 rows in set (0.00 sec)</nowiki> | 47 rows in set (0.00 sec)</nowiki> | ||
+ | |||
+ | ==查看全局字符集== | ||
+ | |||
+ | MariaDB [mxshop]> show variables like '%character%'; | ||
+ | |||
+ | <nowiki>+--------------------------+----------------------------+ | ||
+ | | 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/ | | ||
+ | +--------------------------+----------------------------+</nowiki> | ||
+ | 8 rows in set (0.00 sec) | ||
+ | |||
+ | MariaDB [mxshop]> show variables like 'collation%'; | ||
+ | |||
+ | <nowiki>+----------------------+-------------------+ | ||
+ | | Variable_name | Value | | ||
+ | +----------------------+-------------------+ | ||
+ | | collation_connection | utf8_general_ci | | ||
+ | | collation_database | latin1_swedish_ci | | ||
+ | | collation_server | latin1_swedish_ci | | ||
+ | +----------------------+-------------------+</nowiki> | ||
+ | |||
+ | ==查看数据库字符集== | ||
+ | MariaDB [mxshop]> show create database mydb2; | ||
+ | |||
+ | <nowiki> | ||
+ | |||
+ | +----------+----------------------------------------------------------------+ | ||
+ | | Database | Create Database | | ||
+ | +----------+----------------------------------------------------------------+ | ||
+ | | mydb2 | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET utf8 */ | | ||
+ | +----------+----------------------------------------------------------------+ | ||
+ | </nowiki> | ||
+ | ==设置字符集== | ||
+ | ===创建时设置=== | ||
+ | MariaDB [mxshop]> create database mydb3 CHARACTER SET utf8 COLLATE utf8_unicode_ci; | ||
+ | |||
+ | Query OK, 1 row affected (0.00 sec) | ||
+ | |||
+ | MariaDB [mxshop]> show create database mydb3; | ||
+ | |||
+ | <nowiki>+----------+----------------------------------------------------------------------------------------+ | ||
+ | | Database | Create Database | | ||
+ | +----------+----------------------------------------------------------------------------------------+ | ||
+ | | mydb3 | CREATE DATABASE `mydb3` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ | | ||
+ | +----------+----------------------------------------------------------------------------------------+ | ||
+ | 1 row in set (0.00 sec)</nowiki> | ||
+ | |||
+ | ===创建后设置=== | ||
+ | MariaDB [mxshop]> ALTER DATABASE mydb2 CHARACTER SET utf8 COLLATE utf8_unicode_ci; | ||
+ | |||
+ | <nowiki>Query OK, 1 row affected (0.01 sec)</nowiki> | ||
+ | |||
+ | MariaDB [mxshop]> show create database mydb2; | ||
+ | |||
+ | <nowiki>+----------+---------------------------------------------------------------------------- ------------+ | ||
+ | | Database | Create Database | | ||
+ | +----------+---------------------------------------------------------------------------- ------------+ | ||
+ | | mydb2 | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_un icode_ci */ | | ||
+ | +----------+---------------------------------------------------------------------------- ------------+ | ||
+ | 1 row in set (0.00 sec)</nowiki> | ||
+ | |||
+ | ==查看数据表字符集== | ||
+ | MariaDB [mxshop]> show create table users_userprofile; | ||
+ | |||
+ | <nowiki>+-------------------+----------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------+ | ||
+ | | Table | Create Table | | ||
+ | +-------------------+----------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------+ | ||
+ | | users_userprofile | CREATE TABLE `users_userprofile` ( | ||
+ | `id` int(11) NOT NULL AUTO_INCREMENT, | ||
+ | `password` varchar(128) COLLATE utf8_unicode_ci NOT NULL, | ||
+ | `last_login` datetime DEFAULT NULL, | ||
+ | `is_superuser` tinyint(1) NOT NULL, | ||
+ | `username` varchar(150) COLLATE utf8_unicode_ci NOT NULL, | ||
+ | `first_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL, | ||
+ | `last_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL, | ||
+ | `is_staff` tinyint(1) NOT NULL, | ||
+ | `is_active` tinyint(1) NOT NULL, | ||
+ | `date_joined` datetime NOT NULL, | ||
+ | `name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | `birthday` date DEFAULT NULL, | ||
+ | `gender` varchar(6) COLLATE utf8_unicode_ci NOT NULL, | ||
+ | `mobile` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | PRIMARY KEY (`id`), | ||
+ | UNIQUE KEY `username` (`username`) | ||
+ | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | | ||
+ | +-------------------+----------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------+ | ||
+ | 1 row in set (0.00 sec)</nowiki> | ||
+ | |||
+ | 参考文档:https://www.cnblogs.com/yangmingxianshen/p/7999428.html | ||
+ | |||
+ | https://www.it1352.com/1658430.html |
2020年6月23日 (二) 09:56的最新版本
character set和collation的是什么
character set即字符集
我们常看到的UTF-8、GB2312、GB18030都是相互独立的character set。即对Unicode的一套编码。
collation即比对方法
用于指定数据集如何排序,以及字符串的比对规则。
每个character set会对应一定数量的collation。查看方法是在MySQL的Console下输入:
MariaDB [mxshop]> show collation like 'utf8%';
+--------------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+---------+-----+---------+----------+---------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 | | utf8_unicode_ci | utf8 | 192 | | Yes | 8 | | utf8_icelandic_ci | utf8 | 193 | | Yes | 8 | | utf8_latvian_ci | utf8 | 194 | | Yes | 8 | 。。。 。。。 +--------------------------+---------+-----+---------+----------+---------+ 47 rows in set (0.00 sec)
查看全局字符集
MariaDB [mxshop]> 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 [mxshop]> show variables like 'collation%';
+----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+
查看数据库字符集
MariaDB [mxshop]> show create database mydb2;
+----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | mydb2 | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+
设置字符集
创建时设置
MariaDB [mxshop]> create database mydb3 CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)
MariaDB [mxshop]> show create database mydb3;
+----------+----------------------------------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------------------------------+ | mydb3 | CREATE DATABASE `mydb3` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ | +----------+----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
创建后设置
MariaDB [mxshop]> ALTER DATABASE mydb2 CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.01 sec)
MariaDB [mxshop]> show create database mydb2;
+----------+---------------------------------------------------------------------------- ------------+ | Database | Create Database | +----------+---------------------------------------------------------------------------- ------------+ | mydb2 | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_un icode_ci */ | +----------+---------------------------------------------------------------------------- ------------+ 1 row in set (0.00 sec)
查看数据表字符集
MariaDB [mxshop]> show create table users_userprofile;
+-------------------+----------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------+ | Table | Create Table | +-------------------+----------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------+ | users_userprofile | CREATE TABLE `users_userprofile` ( `id` int(11) NOT NULL AUTO_INCREMENT, `password` varchar(128) COLLATE utf8_unicode_ci NOT NULL, `last_login` datetime DEFAULT NULL, `is_superuser` tinyint(1) NOT NULL, `username` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `first_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `is_staff` tinyint(1) NOT NULL, `is_active` tinyint(1) NOT NULL, `date_joined` datetime NOT NULL, `name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, `birthday` date DEFAULT NULL, `gender` varchar(6) COLLATE utf8_unicode_ci NOT NULL, `mobile` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL, `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------------+----------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------+ 1 row in set (0.00 sec)
参考文档:https://www.cnblogs.com/yangmingxianshen/p/7999428.html