"Go" MySQL Beginner learning five: MySQL character set

Reprint Address: http://www.2cto.com/database/201212/175541.html

MySQL Character set support (Character set supports) has two aspects: Character set (Character set) and Sort method (Collation).  www.2cto.com            support for character sets is refined to four levels: server, database, data table (table), and connection ( Connection).   MySQL default character set          mysql the designation of a character set can be refined to a database, a table, a column, and what character set should be used.           However, traditional programs do not use complex configurations when creating databases and datasheets, and they use the default configuration, so where does the default configuration come from?           (1) When compiling MySQL, a default character set is specified, and this character set is latin1;          (2) When you install MySQL, To specify a default character set in the configuration file (My.ini), and if not specified, this value inherits from the;          (3) specified at compile time when you start 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 which point the character_set_server is set to this default character set;          (4) When a new database is created, unless explicitly specified, The character set of this database is set by default to character_set_server;          (5) When a database is selected, Character_set_database is set to the default character set for this database;          (6) When a table is created in this database, the default character set of the table is set to Character_set_database, That is, the default character set for this database;          (7) whenWhen you set a column in a table, the default character set for this column is the table's character set, unless explicitly specified,  www.2cto.com            Simple summary, if not modified anywhere, Then all the tables of all the databases of all the fields are stored with latin1, but if we install MySQL, we will generally choose multi-language support, that is, the installer will automatically set the Default_character_set in the configuration file to UTF-8, This ensures that by default, all the columns of all the tables in the database are stored with UTF-8.   Two, view character set  1, view MySQL database server and database character set          mysql> show variables like '%char% ';          +--------------------------+------------------------------------------+         | Variable_name            | Value                                 &NBS P  |         +--------------------------+------------------------------------------+          | Character_set_client     | Latin1                                   |--Client Character set          | character_set_connection | Latin1                                 &NB SP; |         | Character_set_database   | Latin1                                 &NB SP; |--database character set          | Character_set_filesystem | Binary                                 &NB SP; |         | Character_set_results    | Latin1                                 &NB SP; |         | Character_set_server     | Latin1                                 &NB Sp |--Server Character set          | Character_set_system     | UTF8                                 &NBSP ;   |         | Character_sets_dir       | D:\APMServ5.2.6\MySQL5.1\share\charsets\ |         +--------------------------+----------- -------------------------------+ 2, viewing the MySQL data table (table) Character set          mysql> Show Table Status from test like '%test% ';         +---------------+--------+---------+------------+    +-------------------+         | Name          | Engine | Version | Row_format |    | Collation         |         +---------------+--------+---------+---------- --+    +-------------------+         | Test_char     | MyISAM |      10 | Fixed      |    | Hebrew_general_ci |         | Test_inn      | InnoDB |      10 | Compact    |    | Latin1_swedish_ci |         | TEST_INN2     | InnoDB |      10 | Compact    |    | Utf8_general_ci   |         | test_priority | MyISAM |      10 | Dynamic    |    | Latin1_swedish_ci |         | Test_trans    | InnoDB |      10 | Compact    |    | Latin1_swedish_ci |         | Test_view     | NULL   |    null | NULL       |    | NULL              |         +---------------+--------+------- --+------------+    +-------------------+  www.2cto.com   3, view the character set of the MySQL data column (column)          mysql> show full columns from v9_picture;         +-------------+-------------- ---------+----------------+         | Field       | Type                  | Collation      |         +-------------+-----------------------+------------- ---+         | ID          | Mediumint (8) unsigned | NULL           |         | CATID       | smallint (5) unsigned  | NULL           |         | typeID      | smallint (5) unsigned  | NULL           |         | Title       | CHAR (80) &nbsp            | Gbk_chinese_ci |         | Style       | Char ($)              | Gbk_chinese_ci |         | Thumb       | char (+)             | Gbk_chinese_ci |         | Keywords    | char (+)              | Gbk_chinese_ci |         | Description | char (255)             | Gbk_chinese_ci |         | Posids      | tinyint (1) unsigned   | NULL           |         | URL         | char (+)             | Gbk_chinese_ci |         | Listorder   | tinyint (3) unsigned   | NULL           |         | Status      | tinyint (2) unsigned   | NULL           |         | Sysadd      | tinyint (1) unsigned   | NULL           |         | Islink      | tinyint (1) unsigned   | NULL           |         | Username    | Char (             |) Gbk_chinese_ci |         | Inputtime   | Int (ten) unsigned      | NULL           |         | UpdateTime  | Int (ten) unsigned      | NULL           |         +-------------+-----------------------+------ ----------+ , set up and modify character set  1, create database specified database character set          mysql> CREATE DATABASE MyDB CHAracter set gb2312;         mysql> use mydb         mysql> show VA Riables like ' character_set_database ';         +------------------------+--------+         | Variable_name          | Value  |         +------------------------+--------+         | Character_set_database | gb2312 |         +------------------------+--------+ 2, modifying the character set of the database           (1), SQL modification          mysql> ALTER DATABASE mydb character set utf8;    & nbsp    mysql> show variables like ' character_set_database ';         +---------------- --------+-------+         | Variable_name          | Value |         +------------------------+-------+&NBsp        | Character_set_database | UTF8  |         +------------------------+-------+          (2), Modify the contents of a file $mysql_home\data\mydb\db.opt           with profile modification           default-character-set=utf8         default-collation=utf8_general_ci                   default-character-set=latin1          default-collation=latin1_swedish_ci          Restart MySQL after  3, modify character set by SQL          mysql> set character_set_client=utf8;         mysql> set Character_ set_connection=utf8;         mysql> set character_set_database=utf8;      & nbsp  mysql> set character_set_results=utf8;         mysql> set character_set_server= utf8;        mysql> set character_set_system=utf8;         mysql> set Collation_ connection=utf8;         mysql> set collation_database=utf8;        & Nbsp;mysql> set Collation_server=utf8;