Character sets in MySQL

[Calan]

You all should be knowing that MySQL, version 4.1, has introduced a highly advanced support for the administration of different character set. In fact allows us to manage the character set at the server, database, table and single column, as well as a client and connection. I am not having much knowledge about the character set that are used in MySQL. So thought that you members will provide some help in this subject. I am expecting some quick responses from your side.

[Ackley]

The character set (character sets) are the different systems through which the alphanumeric characters, punctuation marks and all the symbols displayed on a computer are stored in a binary value. In each set of characters to a binary value corresponds to a very specific character. Therefore, when a string is stored using a certain set of characters will be displayed through the same set, otherwise some characters may look different from how we expect them. The classic example of this problem typically occurs with accented letters and, at times, with the Euro symbol, which happens every so often to see not properly represented, for example on a web page, a sign that we are not displaying the page with the right set of characters. For each character set are associated with one or more collations, which are the possible ways to compare strings of characters that are part of that character set.

[Mecurtis]

For each character set are associated with one or more collations, which are the possible ways to compare strings of characters that are part of that character set.

As an example we can then say that a particular table uses the character set latin 1 (the most widely used in Western Europe) and the collation latin1_general_cs. This collation is multilingual (ie not specific to one language) and "case sensitive" as evidenced by the "cs" at the end, that takes into account the difference between uppercase and lowercase letters in the order or compare strings. In general we can say that the name of each collation follows a certain standard, starting with the name of the character set to which it refers, usually includes a specification for a language, and ending with cs (case sensitive) or there (case insensitive ) depending on whether or not taking into account the difference between uppercase and lowercase letters, or with bin binary value when the character is used directly for comparisons.

[Khushal]

So we will have, for example, for the latin1_some-language_ci collation of latin1 case insensitive, and latin1_some-language_ci for collation, always case insensitive, rule-based that language DIN-2, while the collation is utf8_bin binary encoding utf8 (Unicode). Normally, the existence of a collation dedicated to a single language is when the general rules of the character set does not satisfy the requirements of that language. The collation devoted to that language, for example, are in the right way to deal with the vowels with umlauts (eg U) and the letter "sharp" (ß). The SQL statement "SHOW CHARACTER SET and SHOW COLLATION" allow us to obtain the list, respectively, of the character set and collation on the server. The first shows, for each set, the default collation. The second we can see which character set they belong: it is clear from what we have said so far, every collation is tied to a single character set.

[DllD]

As mentioned, MySQL handles character set and collation at several levels: server, database, table, column. Speaking of stored data, of course, what is relevant is what charset is used for each column (CHAR, VARCHAR or TEXT). All values of the higher level, then, are only intended to function as a default for the respective lower level. At the server level and we default_character_set default_collation variables, for which the usual rules apply for the variables of the system: for example, can therefore be initialized by an option file, and even modified at runtime. In the absence of initializing the default charset is latin1 with latin1_swedish_ci collation. Now let's see some examples of SQL definitions:

CREATE DATABASE db1 [CHARACTER SET utf8] [COLLATE utf8_general_ci];
 CREATE TABLE table1 (
         Column1 VARCHAR (5) CHARACTER SET latin1 COLLATE latin1_german_ci,
         column2 VARCHAR (5) CHARACTER SET latin1,
         column3 VARCHAR (10)
 ) [DEFAULT CHARACTER SET latin1 [COLLATE latin1_general_ci]];

[Astro121]

Not indicate the collation would be irrelevant, because utf8_general_ci is the default to utf8. Does not indicate it would mean to use utf8 as default for the database, the values of the server. Keep in mind that the two values are traveling coupled in the sense that you can not transmit "down" whatever the default collation from that of character set, seen on the ground before any collation is tied to a single character set. Thus, at every level, or both values are inherited from the top level, or, once established the charset, collation, if not explicitly expressed, will be the default for that charset. The system variables character_set_client, character_set_results represent, respectively, the charset of incoming instructions from the client and what will be used to send the answers and we then character_set_connection collation_connection, which are used by the server to convert the received instructions to make comparisons between the constants string.