The database needs to accommodate a variety of languages and characters to support different character sets (Character set), and each character set has its own collation (Collation).
(Note: Collation originally intended for proofreading, collation, but according to the actual use of the scene, think or translation as a collation more appropriate)
In most cases, the type of character set and collation used determines the level of the server, database, and table, and general SQL operations do not need to be concerned about these.
The following operations are all examples of MySQL.
To view the character set and collation supported by the database
To view the character set:
SET;
Some of the results:
To view the collation: (suffix "_cs" or "_ci" means case-sensitive and case-insensitive (sensitive. & Case Insensitve))
SHOW COLLATION;
Some of the results:
Using the character set and collation supported by the database
Typically, system administration defines a default character set and collation at installation time.
You can also set character sets and collations at the table level, or even at the column level, when you create a database, on a database-wide scale, and on a table.
> Specify character set and collation for database construction :
To determine the character set and collation used, you can use the following statements:
like ' character% ' like'collation%';
> Specify a table's character set and collation when you build a table :
1 CREATE TABLE mytable (2 INT , 3 VARCHAR (ten) 4 DEFAULT CHARACTER SET Hebrew 5 COLLATE hebrew_general_ci;
Use the database default setting when you do not specify a character set and collation, or use the default collation of a character set if you specify a character set that does not specify a collation.
> specifying the character set and collation of tables and columns when you build a table
CREATE TABLE mytable ( INT, varchar () ,varchar (CHARACTERSET latin1 DEFAULTCHARACTER SET Hebrew COLLATE hebrew_general_ci;
> Sorting Rules for order by in a custom query statement
SELECT * from ORDER by ' column1 ' COLLATE latin1_general_cs;
The use here is not limited to order by, but also group by, aggregate functions, and so on.
Reference article:
"1"MySQL will be January 2009 11th Edition 27th chapter "Globalization and Localization"
Database character set and collation (Character set and Collation)