MySQL Character Set Learning notes

MySQL includes character set support this enables you to store data using a variety of character sets and perform Comparis ONS according to a variety of collations. You can specify character. MySQL supports the use of character

MySQL supports Server,databases,table,column-level character sets and sorting methods. These settings have a table record in the Information_schema library, COLLATION

SHOW VARIABLES like ' character_set% ';
SHOW VARIABLES like ' collation% ';

mysql> use information_schemamysql>  show tables like  '%coll% '; +---------------------------------------+| tables_in_information _schema  (%coll%)  |+---------------------------------------+| collations                              | |  collation_character_set_applicability |+---------------------------------------+mysql> show  tables like  '%sets% '; +---------------------------------------+| tables_in_information_ schema  (%sets%)  |+---------------------------------------+| character_sets                          |+---------------------------------------+ 

The mysql server can support multiple character sets. to list  the available character sets,use the SHOW CHARACTER SET  Statement. a partial listing follows. for more complete information,  see Character Sets and Collations That MySQL Supports.mysql>  show character set;+----------+-----------------------------+---------------------+--------+|  charset | description | default collation | maxlen |+----------+------ -----------------------+---------------------+--------+| big5 | big5 traditional  chinese | big5_chinese_ci | 2 | |  dec8 | dec west european | dec8_swedish_ci | 1 | |  cp850 | dos west european | cp850_general_ci |  1 |. any given character set always has at least one collation.  It may have several collations. To list the collations for a character set, use the show  collation statement. for example,to see the collations for the  latin1  (Cp1252 west european)  character set, use this statement  to find those collation names that begin with latin1:mysql>  SHOW COLLATION LIKE  ' latin1% ', +---------------------+---------+----+---------+----------+ ---------+| collation | charset | id | default | compiled |  sortlen |+---------------------+---------+----+---------+----------+---------+| latin1_german1 _ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | yes | yes | 1  |

View character set and sort by: show CHARACTER set; SHOW COLLATION like ' character name '

Each character set has a default sorting method.

Server-level settings can be restarted by the server, or compiled, or configured in a configuration file

[Mysqld]character-set-server=utf8collation-server=utf8_general_ci

mysql server has a server character set and a server  Collation. these can be set at server startup on the command  line or in an option file and changed at runtime. Initially, the server character set and collation depend on the  options that you use when you start mysqld. You can  Use --character-set-server [396] for the character set. along with  it,you can add --collation-server [396] for the collation. if  you don ' t specify a character set,that is the same as  saying --character-set-server=latin1 [396]. if you specify only a  character set  (For example, latin1)  but not a collation, that is the same as  saying --characterset-server=latin1 [396] --collation-server=latin1_swedish_ci [396]  because latin1_swedish_ci is the default collation for latin1.  Therefore, the following three commands all have the same effect:

Shell> mysqld
Shell> mysqld--character-set-server=latin1
shell> mysqld--character-set-server=latin1 \ \
--collation-server=latin1_swedish_ci

One-to-change the settings are by recompiling. The default server CharacterSet and collation when building from sources, use the Default_charset [a] and DEFA ult_collation [126] options for CMake. For example:specifying Character sets and collations

Shell> CMake. -ddefault_charset=latin1
Pre
Shell> CMake. -ddefault_charset=latin1 \ \
-ddefault_collation=latin1_german1_ci

Both mysqld and CMake Verify that the character set/collation combination are valid. If Not,each Program displays an error message and terminates. The server character set and collation are used as default values if the database CharacterSet and collation is not speci Fied in CREATE DATABASE statements. They has no other purpose. The current server character set and collation can is determined from the values of thecharacter_set_server [445] and Coll Ation_server [447] System variables. These variablescan is changed at runtime.

Databases level

Every database has a database character set and a database collation. The CREATE database and ALTER DATABASE statements has optional clauses for specifying the database character set and Coll Ation: Can be modified by creating DB or later by ALTER DATABASE

CREATE database Db_name[[default] CHARACTER SET Charset_name][[default] COLLATE collation_name]alter DATABASE db_name[[ DEFAULT] CHARACTER SET Charset_name][[default] COLLATE collation_name]

The keyword SCHEMA can be used instead of the DATABASE.
All database options is stored in a text file named db.opt The can is found in the database
Directory. Each library has a db.opt save character set and sort method
The CHARACTER SET and COLLATE clauses make it possible to create databases with different
Character sets and collations on the same MySQL server.
Example:

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Following is the set of character set and sorting, choose which to take effect of the problem, basically db,table,colum is this rule
If both CHARACTER set X and COLLATE y is specified, CHARACTER set X and Collation Y are used.
If CHARACTER set X is specified without COLLATE, CHARACTER set X and its default collation is
Used. To see the default collation-character set, use the SHOW collation statement.
If COLLATE y is specified without CHARACTER set, the CHARACTER set associated with Y and
Collation Y is used.
Otherwise, the server character set and server collation is used.
The database character set and collation is used as default values for table definitions if the table character set and C Ollation is not specified in CREATE TABLE statements. The database character set also is used by LOAD DATA INFILE.

Table Character Set and Collation
Every table has a table character set and a table collation. The CREATE table and ALTER table
Statements has optional clauses for specifying the table character set and collation:

CREATE table Tbl_name (column_list) [[DEFAULT] CHARACTER SET charset_name][collate collation_name]]alter table tbl_name[ [DEFAULT] CHARACTER SET charset_name][collate collation_name]example:create TABLE T1 (...) CHARACTER SET latin1 COLLATE latin1_danish_ci;

Every "character" column (that's, a column of type CHAR, VARCHAR, or TEXT) has a column character set and a column Collat Ion. Column definition syntax for CREATE table and ALTER table have optional clauses for specifying the column character set and Collation

col_name {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER set charset_name][collate Collation_name]these clauses can also is used for ENUM and SET Colu Mns:col_name {ENUM | Set} (val_list) [CHARACTER set charset_name][collate collation_name]examples:create TABLE T1 (col1 VARCHAR (5) CHARACTER SET latin1collate latin1_german1_ci); ALTER TABLE T1 MODIFYcol1 VARCHAR (5) CHARACTER SET latin1specifying CHARACTER sets and Collations901collate latin1_swedish _ci;

CREATE TABLE T1 (col1 CHAR (Ten) CHARACTER set UTF8 COLLATE utf8_unicode_ci) CHARACTER set latin1 COLLATE latin1_bin;

CREATE TABLE T1 (col1 CHAR (Ten) CHARACTER set UTF8) CHARACTER set latin1 COLLATE latin1_bin;

CREATE TABLE T1 (col1 CHAR (Ten) COLLATE utf8_polish_ci) CHARACTER SET latin1 COLLATE latin1_bin;

CREATE TABLE T1 (col1 CHAR (Ten)) CHARACTER SET latin1 COLLATE latin1_bin;

Neither the character set nor collation is specified for the column,

So the table defaults is used.
The column has character set latin1 and collation Latin1_bin.
The MySQL client programs MySQL, Mysqladmin, Mysqlcheck, Mysqlimport, and Mysqlshow
Determine the default character set to use as follows: these programs

For example, character character name uses and takes effect example:suppose that column1 is defined as CHAR (5) Character SET latin2. If you does not say
Set NAMES or set CHARACTER set, then for SELECT column1 from T, and the server sends back
All of the values for column1 using the character set, the client specified when it connected. On the other hand, if you say set NAMES ' Latin1 ' or set CHARACTER set latin1 before issuing the SELECT statement, the Serv Er converts the latin2 values to latin1 just before sending results back. Conversion may lossy if there is characters that is not in both character sets.

MySQL Character Set Learning notes