MySQL Learning-character set selected, modified.

Basic concepts:


A character (Character) is the smallest of the ideographic symbols in a human language. such as ' A ', ' B ' and so on;
Encoding (Encoding) is given a series of characters, assigning a numeric value to each character, and representing the corresponding character with a numeric value. For example, we give the character ' a ' a value of 0, give the character ' B ' a value of 1, then 0 is the character ' a ' encoding;
The character set (Character set) refers to a collection of characters and encoded pairs.
The word Fu She (Collation) refers to the comparison rule between characters within the same character set.
The word Fu She name in MySQL follows the naming convention: begins with the character set name of the word Fu She, _ci (which is case insensitive), _cs (which is case sensitive), or _bin (which means comparison by encoded value). For example: Under the word Fu She "Utf8_general_ci", the characters "a" and "a" are equivalent;



Mysql> Show variables like ' character% '; --View MySQL character set setting for MySQL character set
+--------------------------+----------------------------+
| variable_name |     Value | ? System variables:
+--------------------------+----------------------------+
| character_set_client |     UTF8 | –character_set_client: The character set used by the client source data
| character_set_connection |     UTF8 | –character_set_connection: Connection Layer Character Set
| Character_set_database |     Latin1 | –character_set_database: The default character set for the currently selected database
| Character_set_filesystem |     binary | –character_set_filesystem: File system Character Set
| Character_set_results |     UTF8 | –character_set_results: Query result Character Set
| Character_set_server |     Latin1 | –character_set_server: Internal operation character set (server's character set)
| Character_set_system |     UTF8 | –character_set_system: System metadata (field name, etc.) character set
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
8 rows in Set (0.00 sec)


Detailed Explanation:
1.
The character set and collation of the server (data) end can be divided into four levels to specify--server, database, table, and column. When MySQL accesses data in a column (columns), if the character set and collation of column are not specified, the table is traced upward, and if the table also does not specify a character set and collation, The database's character set and collation are the default values, and if database is still not specified, the server's character set and collation are the default values.


2.
Where does the default value for the character set and collation of the server come from? The answer is that both the configuration file (My.ini) and the mysqld (or mysqld-nt) command-line arguments can be specified. If, unfortunately, you do not specify at all on the My.ini or command line, MySQL will use the default character set--latin1 specified when compiling MySQL.


3.
Data Character Set conversion process:
Character_set_client->character_set_connection->character_set_ (Column,table,database,server) Character_set_results






MySQL Default character set
MySQL specifies that the character set can be refined to a single database, a table, and a column. Traditional programs do not use a complex configuration when creating databases and data tables, they use the default configuration.
(1) When compiling MySQL, a default character set is specified, and this character set is latin1;
(2) When installing MySQL, you can specify a default character set in the configuration file (My.ini), and if not specified, this value is inherited from the compile-time specified;
(3) When starting mysqld, you can specify a default character set in the command line arguments, if not specified, this value inherits from the configuration in the configuration file, at this time Character_set_server is set to this default character set;
(4) Install MySQL Select Multi-language support, the installer will automatically set the Default_character_set in the configuration file to UTF-8, to ensure that by default all the tables of all the columns of the database with UTF-8 storage.




Mysql> CREATE DATABASE demo; --Create a database without specifying a character set and create a database using the default character set
Query OK, 1 row Affected (0.00 sec)


mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Demo |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
5 rows in Set (0.00 sec)


Mysql> Show Create database demo \g; --See what character set to use when creating the database.
1. Row ***************************
Database:demo
Create database:create Database ' demo '/*!40100 DEFAULT CHARACTER SET latin1 */
1 row in Set (0.00 sec)


ERROR:
No query specified


Mysql> CREATE DATABASE Demo default character set GBK collate gbk_chinese_ci; --Specifying a character set to create a database
Query OK, 1 row Affected (0.00 sec)
Mysql> Show Create DATABASE demo;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| Demo | CREATE DATABASE ' demo '/*!40100 DEFAULT CHARACTER SET GBK */|
+----------+--------------------------------------------------------------+
1 row in Set (0.00 sec)


Mysql> CREATE DATABASE Demo2 default character set UTF8 collate utf8_general_ci; --Specifying a character set to create a database
Query OK, 1 row Affected (0.00 sec)


Modify Character Set: (after modifying the database character set, if you need to use old data, then further processing is required)

Method One:
1. Find out if there are my.cnf files in the/etc directory;
#ls-L | grep my.cnf (Find out if a my.cnf file exists in/etc)
2. If there is no need to copy from/usr/share/mysql, one to/etc, there are five suffix. cnf files in the/usr/share/mysql directory, respectively my-huge.cnf MY-INNODB-HEAVY-4G.CNF MY-LARGE.CNF my-medium.cnf my-small.cnf, from which I randomly copy one to/etc directory and change it to my.cnf file, I chose MY-MEDIUM.CNF:
#cp/usr/share/mysql/my-medium.cnf/etc/my.cnf
3. Modify the My.cnf file, add Default-character-set=utf8 ([client] [mysqld] [MySQL] in three places in the file.
#vi/etc/my.cnf


Method Two: (Temporary entry into force)
Modified via MySQL command line:


Mysql> set Character_set_client=utf8;


Query OK, 0 rows Affected (0.00 sec)


Mysql> set Character_set_connection=utf8;


Query OK, 0 rows Affected (0.00 sec)


Mysql> set Character_set_database=utf8;


Query OK, 0 rows Affected (0.00 sec)


Mysql> set Character_set_results=utf8;


Query OK, 0 rows Affected (0.00 sec)


Mysql> set Character_set_server=utf8;


Query OK, 0 rows Affected (0.00 sec)


Mysql> set Character_set_system=utf8;


Query OK, 0 rows affected (0.01 sec)


Mysql> set Collation_connection=utf8;


Query OK, 0 rows affected (0.01 sec)


Mysql> set Collation_database=utf8;


Query OK, 0 rows affected (0.01 sec)


Mysql> set Collation_server=utf8;


Query OK, 0 rows affected (0.01 sec)




Summarize:
1. If you do not specify a database character set when you install MySQL, the default database character set is latin1.
2. A character set is selected according to the development program when the database character set is selected.

MySQL Learning-character set selected, modified.