Mysql Character Set and Verification Analysis

Mysql character set and verification analysis use case first let's look at an example: there is a user table www.2cto.com User (username VARCHAR (5) character set utf8COLLATE utf8_general_ci); There is a record ('username '); and ('username'); Use select * from User where UserNAme = 'username'; two pieces of data will be selected and the COLLATE of username will be changed to utf8_bin; User (username VARCHAR (5) character set utf8 COLLATE utf8_bin); then select * from User where username = 'username'; the first data entry will be selected. Why? The character set is a SET of symbols and encodings. COLLATE is a set of rules used to compare characters in character sets. Let's use an example of a hypothetical character set to make the difference clear. Assume that we have an alphabet with four letters: 'A', 'B', 'A', and 'B '. We assign A value for each letter: 'A' = 0, 'B' = 1, 'A' = 2, 'B' = 3. The letter 'A' is A symbol, and the number 0 is the encoding of 'A'. These four letters and Their encoding are combined into A character set. Suppose we want to compare the values of two strings: 'A' and 'B '. The simplest way to compare is to find the encoding: 'A' is 0, and 'B' is 1. Because 0 is less than 1, we can say that 'A' is less than 'B '. We only apply a verification rule to our character set. The checking rule is a set of rules (in this case, it is only a set of rules): "compare the encoding ." We call the simplest proofread rule among all these possible rules a binary (binary) proofread rule. But what if we want lower-case letters to be equivalent to upper-case letters? Then, we have at least two rules: (1) the lowercase letters 'A' and 'B' are considered to be equivalent to 'A' and 'B'; (2) then compare the encoding. This is a case-insensitive checking rule. This is more complex than binary verification rules. Character Set and verification supported by MySQL http://dev.mysql.com/doc/refman/5.1/zh/charset.html#charset-charsets The server Character Set and proofreader MySQL server have a server character set and a server verification rule. They cannot be set to null. MySQL uses the following methods to determine the server Character Set and server verification rules: · when the server is started, it is set based on valid options. · The method is very easy to determine at the server level based on the runtime setting value. When mysqld is started, the server Character Set and proofreading rules are determined based on the initial options used. You can use -- default-character-set to set the character set, and you can add -- default-collation to the proofreading rule after the character set. If no character set is specified, it is the same as -- default-character-set = latin1. If you specify only one character set (for example, latin1) but not one verification rule, it is the same as -- default-charset = latin1 -- default-collation = latin1_swedish_ci, because latin1_swedish_ci is the default proofreading rule of latin1. Therefore, the following three commands have the same effect: www.2cto.com shell> mysqld -- default-character-set = latin1 shell> mysqld -- default-character-set = latin1 \ -- default-collation = latin1_swedish_ci by re-compiling. If you want to change the default server Character Set and verification rules when building from the source program, use -- with-charset and -- with-collation as configure parameters. Example: shell>. /configure -- with-charset = latin1 or: shell>. /configure -- with-charset = latin1 \ -- with-collation = latin1_german1_ci mysqld and configure both verify whether the character set/collation combination is valid. If it is invalid, each program will display an error message and terminate it. The current server Character Set and verification rules can be used as the value of character_set_server and collation_server system variables. The value of these variables can be changed at runtime. Each database has a database character set and a database verification rule. It cannot be empty. The create database and alter database statements have an optional clause to specify the DATABASE character set and collation:
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] example: create database db_name default character set latin1 COLLATE latin1_swedish_ci; MySQL selects the database character set and DATABASE proofreading rules as follows: · if character set x and collate y are specified, character set x and collation Y are used. · If character set x is specified but collate y is not specified, character set x and character set x are used as the default proofreading rules. · Otherwise, server character sets and server verification rules are used. The create database... default character set... syntax of www.2cto.com MySQL is similar to the create schema... syntax of standard SQL. Therefore, you can create a database on the same MySQL server that uses different character sets and verification rules. If the TABLE Character Set and collation are not specified in the create table statement, the database character set and collation are used as the default values. They have no other purpose. The character set and collation rules of the default database can be used as character_set_database and collation_database system variables. The server sets the values of these two variables whenever the default database is changed. If no default database exists, these two variables have the same value as the corresponding server-level variables (character_set_server and collation_server. Each table has a table character set and a verification rule. It cannot be empty. To specify the TABLE Character Set and collation, the create table and alter table statements have an optional clause: 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 (...) default character set latin1 COLLATE latin1_danish_ci; MySQL selects the table character set and proofreading rules as follows: · if character set x and collate y are specified, character set x and collate y are used. Www.2cto.com. If character set x is specified but collate y is not specified, the character set x and character set x are used as the default proofreading rules. · Otherwise, server character sets and server verification rules are used. If the column Character Set and collation are not specified in the column definition, the table Character Set and collation are used by default. Table character sets and proofreading rules are extensions of MySQL; they are not in standard SQL. The column Character Set and each "character" Column (that is, a CHAR, VARCHAR, or TEXT column) must have a column character set and a column verification rule. It cannot be empty. The column definition syntax has an optional clause to specify the column character set and proofreading rules: col_name {CHAR | VARCHAR | TEXT} (col_length) [character set charset_name [COLLATE collation_name] example: create table Table1 (column1 VARCHAR (5) character set latin1 COLLATE latin1_german1_ci); MySQL selects the column character set and proofreading rules as follows: www.2cto.com if character set x and collate y are specified, use character set x and collate y. · If character set x is specified but collate y is not specified, character set x and character set x are used as the default proofreading rules. · Otherwise, use the table Character Set and server verification rules. The character set and COLLATE clauses are standard SQL statements.