Summary of MySQL coding problems

Summary of MySQL coding problems

Previous
/
Next article
2007-11-05 14:39:12

/Personal classification: others' rocks

View (882)
/
Comment (1)
/
Score (0
/0
)

Full text from [http://hi.baidu.com/kkwtre/blog/item/c5ef410254b732a30cf4d2ba.html]

1. GeneralCharacter Set


And Proofreading

Mysql5.1 can do these tasks:

· Use multiple character sets to store strings

· Use multiple checking rules to compare strings

· On the same server, the sameDatabase


Or even use different character sets or proofreaders to mix strings in the same table.

· Allow definition of character sets and verification rules at any level

2.MySQL


Character Set and verification in

The MySQL Server supports multiple character sets. You can use the show Character Set statement to list available character sets:

Mysql> show Character Set

3. Determine the default Character Set and Verification

Character Set and verification rules have four default settings: Server-level, database-level, table-level, and connection-level. The following descriptions may be complex, but in actual applications, we can find that using multiple levels will naturally produce obvious results.

MySQL is as follows:Method


Determine the server Character Set and server verification rules:

· Set valid options when the server is started

· Set the value according to the running time

In
Server level. The method is simple. When mysqld is started, the server Character Set and
Proofread rules. You can use -- default-character-set to set the character set.
Add -- default-collation to the proofreading rule. If no character set is specified, it is in the same phase as -- default-character-set = Latin1
.

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.

 

3.2. database character set and Verification

Each database has a database character set and a database proofreading rule. It cannot be blank. 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]
For example:

Create Database db_name
Default Character Set Latin1 collate latin1_swedish_ci;

MySQL selects the database character set and database verification rules as follows:

· If character set X and collate y are specified, character set X and checking rule 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.

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.

3.3. Table Character Set and Proofreading

Each table has a table character set and a proofreading rule. It cannot be blank. 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]
For 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.

· 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 database character set and collation as the default value.

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.

3.4. Column Character Set and Verification

Each "character" Column (that is, a char, varchar, or text column) has a column character set and a column validation rule. It cannot be blank. 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]
For example:

Create Table Table1

(

Column1 varchar (5) Character Set Latin1 collate latin?german=ci

);

MySQL selects the column Character Set and proofreading rules as follows:

· If character set X and collate y are specified, character set X and collate 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, use the table Character Set and server verification rules.

The character set and collate clauses are standard SQL statements.

For example:

Example 1: Table and column Definitions

Create Table T1
(
C1 char (10) Character Set Latin1 collate latin?german=ci
) Default Character Set latin2 collate latin2_bin;
Here we have a column that uses the Latin1 character set and latin?german=ci proofreading rules. Is an explicit definition, so it is simple and clear. Note that there is no problem in storing a Latin1 column in a latin2 table.

Example 2: Table and column Definitions

Create Table T1
(
C1 char (10) Character Set Latin1
) Default Character Set Latin1 collate latin1_danish_ci;
This
At this time, we have a column using the Latin1 character set and a default proofreading rule. Although it looks natural, the default proofreading rules are not table-level. On the contrary, because Latin1's default checking rules are always
Latin1_swedish_ci, column C1 has a proofreading rule latin1_swedish_ci (instead of latin1_danish_ci ).

Example 3: Table and column Definitions

Create Table T1
(
C1 char (10)
) Default Character Set Latin1 collate latin1_danish_ci;
We have a column that uses a default character set and a default proofreading rule. In this case, MySQL queries the table level to determine the column Character Set and proofreading rules. Therefore, the character set of column C1 is Latin1, and its calibration rule is latin1_danish_ci.

Example 4: database, table, and column Definitions

Create Database d1
Default Character Set latin2 collate latin2_czech_ci;
Use D1;
Create Table T1
(
C1 char (10)
);
Me
You have created a column with no character set or verification rules specified. We have not specified table-level character sets and verification rules. In this case, MySQL searches for database-level settings. (The database settings change
Table settings, which are then changed to column settings .) Therefore, the character set of column C1 is latin2, and its calibration rule is latin2_czech_ci.

3.6. Connecting character sets and checking

Some character sets and checking rule system variables are related to the interaction between the client and the server.
· The server Character Set and verification rules are represented as the values of character_set_server and collation_server variables.

· The character set and proofreading rules of the default database are represented as the values of character_set_database and collation_database variables.

Consider what a "connection" means when connecting to the server. The client sends SQL statements, such as queries, to the service through connections.
. The server sends a response to the client through a connection, such as a result set. For client connection, this will cause some character sets and
The problems of checking rules can be solved through system variables:

· When the query leaves the client, which character set is used in the query?

A: The server uses the character_set_client variable as the character set used in the query sent by the client.

· What character set should the server convert to after receiving the query?

A: during conversion, the server uses character_set_connection and collation_connection system variables.
It converts the query sent by the client from character_set_client system variable to character_set_connection

· Which character set should the server convert to before sending result sets or returning error messages to the client?

A: The character_set_results variable indicates that the server returns the query result to the character set used by the client. Including result data,
For example, the column value and result metadata (such as the column name ).

Two statements affect the character set:
Set names 'charset _ name'
Set Character Set charset_name
Set names displays the character sets used in the SQL statements sent by the client.

The Set names 'X' statement is equivalent to the three statements:
Mysql> set character_set_client = X;
Mysql> set character_set_results = X;
Mysql> set character_set_connection = X;

From:
Http://java.ccidnet.com/art/3737/20060605/571481_1.html

MySQL Character Set

1. display character sets

Mysqladmin-uroot-proot variables | grep character

| Character_set_client | Latin1 |

| Character_set_connection | Latin1 |

| Character_set_database | Latin1 |

| Character_set_results | Latin1 |

| Character_set_server | Latin1 |

| Character_set_system | utf8 |

| Character_sets_dir |/usr/local/share/MySQL/charsets/|

Database uses Latin1 (ISO-8859) by default)

Ii. configuring character sets

1. Configure character sets when creating a database

Create Database testxxx default charset = utf8

3. Configure character set during table Creation

Create Table 't_ agent '(

'Id' smallint (5) not null

) Default charset = utf8;

4. Modify Character Set

Windows Platform

The MySQL configuration file in Windows is my. ini, which is usually in C:/Windows/My. ini or C:/winnt/My. ini.

Default-character-set = GBK # Or gb2312, big5, utf8

Then restart MySQL

Service MySQL restart

Or

/Etc/init. d/MySQL restart

Or useOthers


Method to restart.

[Edit] UNIX platform

Linux


The MySQL configuration file under is my. CNF, which is usually/etc/My. CNF. If you cannot find it, run the find command to find it:

Find/-INAME my. CNF

Add

Default-character-set = GBK # Or gb2312, big5, utf8

Then restart MySQL

3. Configure the sorting Character Set

Myisamchk-r-Q -- Set-character-set = charset

From: http://imysql.cn /? Q = node/20

Introduction to MySQL Character Set
First of all, this article is purely my personal experience, applicable to my common environments and projects.
Personally, we recommend that you use utf8 (Html


The page corresponds to UTF-8), so that your data can be smoothly migrated, because the utf8 character set is currently the most suitable character set for conversion between different character sets, although you may not be able to correctly view the content in the database on the command line tool, I strongly recommend that you use utf8 as the default character set.
The following is a complete example:
1. Create a database table

Mysql> create database if not exists my_db default charset utf8 collate utf8_general_ci;
# Note the following sentence "collate utf8_general_ci", which roughly means sorting by utf8 validation set during sorting
# The default character set of all data tables created in this database will be utf8.

Mysql> Create Table my_table (name varchar (20) not null default '') type = MyISAM default charset utf8;
# This statement creates a table. The default character set is utf8.

2. Write Data

Example 1 is throughPHP


Insert data directly:
A. php
<? PHP
Mysql_connect ('localhost', 'user', 'Password ');
Mysql_select_db ('My _ db ');

// Note that this step is critical. Without this step, all data reads and writes Will be incorrect.
// It sets the default character set for data transmission during the database connection.
// Other programming languages/interfaces are similar, such as. Net/C #/ODBC
// For JDBC, set the connection string to "JDBC: mysql: // localhost/DB? User = user & Password = 123456 & useunicode = true & characterencoding = UTF-8"

Mysql_query ("set names utf8 ;");
// You must convert gb2312 (locally encoded) to UTF-8. You can also use the iconv () function.
Mysql_query (mb_convet_encoding ("insert into my_table values ('test');", "UTF-8", "gb2312 "));
?>

The example is to submit the insert data through the page 2:
B. php
<? PHP
// Output this page encoded as UTF-8
Header ("Content-Type: text/html; charset = UTF-8 ");

Mysql_connect ('localhost', 'user', 'Password ');
Mysql_select_db ('My _ db ');

Mysql_query ("set names utf8 ;");
If (isset ($ _ request ['name '))
{
// Because the character set on this page has been specified as UTF-8, no conversion encoding is required.
Mysql_query (sprintf ("insert into my_table values ('% s');", $ _ request ['name']);
}

$ Q = mysql_query ("select * From my_table ");
While ($ r = mysql_fetch_row ($ q ))
{
Print_r ($ R );
}
?>

<Meta http-equiv = "Content-Type" content = "text/html; charset = UTF-8">
<Form action = "" method = "Post">
<Input type = "text" name = "name" value = ">
<Input type = "Submit" value = 'submit '>
</Form>

Since then, the complete example of using the utf8 character set has ended.
If you want to use gb2312 encoding, we recommend that you use Latin1 as the default Character Set of the data table so that you can insert data directly in the command line tool in Chinese and display it directly. do not use the gb2312 or GBK character sets. If you are worried about query sorting and other issues, you can use the Binary Attribute constraints, such:
Create Table my_table (name varchar (20) binary not null default '') type = MyISAM default charset Latin1;

Appendix 1: Methods for upgrading old data
Take the original character set Latin1 as an example to upgrade it to the utf8 character set. Original table: old_table (default charset = Latin1), new table: new_table (default charset = utf8 ).
Step 1: Export old data
Mysqldump -- default-character-set = Latin1-hlocalhost-uroot-B my_db -- tables old_table> old. SQL
Step 2: Convert the encoding (similar to the Unix/Linux environment)
Iconv-T UTF-8-F gb2312-c old. SQL> New. SQL
You can also remove the-F parameter to enable iconv to automatically determine the original character set.
Iconv-T UTF-8-c old. SQL> New. SQL
Here, we assume that the original data is gb2312 by default.
Step 3: Import
Modify the old. SQL statement and add an SQL statement before the insert/update statement starts:

"Set names utf8;", save.
Mysql-hlocalhost-uroot my_db <New. SQL
Success !!

Appendix 2: MySQL clients that support viewing utf8 character sets have
1.) mysql-front, it is said that this project has been ordered by MySQL AB to stop. For some reason, if there are many cracked versions in China, you can download it (it does not mean I recommend using the cracked version:-P ).
2.) navicat, another very good MYSQL client, just came out of the Chinese version and invited me to try it out. In general, it is still good, but it also needs to be paid.
3.) phpMyAdmin,Open Source


PHP project, very good.
4) Linux terminal tool (Linux terminal), set the character set of the terminal to utf8, connect to MySQL, execute set names utf8; can also read and write utf8 data.

Step 6: Solve the MySQL Character Set Problem (PhP UTF-8 garbled problem) Big | medium | small tags:Apache


, PHP, server configuration
Step 6 solve the MySQL Character Set Problem (PhP UTF-8 garbled problem)

1. My. ini:
[MySQL]
Default-character-set = utf8
[Mysqld]
Default-character-set = utf8
Default-storage-engine = MyISAM
Add the following under [mysqld:
Default-collation = utf8_bin
Init_connect = 'set names utf8'

2. Add
Mb_internal_encoding ('utf-8 ');

3. Add engine = MyISAM default charset = utf8 collate = utf8_bin to the last side of create table.

4. PHPmyAdmin/config. Inc. php
$ Cfg ['defaultcharset'] = 'utf-8 ';
$ Cfg ['recodingengine '] = 'iconv ';

5. PHPmyAdmin/libraries/string. Lib. php
Set
Mb_internal_encoding ($ globals ['charset']);
Changed to: mb_internal_encoding ('utf-8 ');

6. When phpadmin exports data
Remove the "display in hexadecimal format in binary ".

Summary:

Run show variables like 'charater _ set % 'to view various MySQL Character Set settings:

Which affects data transmission between servers and clients?

Character_set_client determines the character set used by the server to receive data sent by the client.
The character_set_connection server converts data sent from the client from character_set_client to character_set_connection.
Character_set_results determines the character set used by the server to send data to the client.

Most articles on the Internet refer to two methods to specify the character set used for transmission.

Set names 'charset _ name'
Set Character Set charset_name

Set names 'X' is equivalent
Set character_set_client = X;
Set character_set_results = X;
Set character_set_connection = X;

Set Character Set 'X' is equivalent
Set character_set_client = X;
Set character_set_results = X;
Set collation_connection =collation_database; -- collation of the database

Institute
If the default character set on the client and server is inconsistent, You need to query a set statement before reading and writing data.
Character Set 'X' or set names' x', and 'X' are the character used by the client.
Set. You can also use mysqli_set_charset (PHP 5> = 5.1.0rc1)

From
Http://kubbs.net/showart.asp? Art_id = 16989 & cat_id = 34

MySQL and later versions support character sets in four levels: Server, database, table, and connection ):
Character_set_server: This is the character set used by the server.
Character_set_client: Set the character set used by the client for sending and querying.
Character_set_connection: This is the character set for the server to convert the received query string
Character_set_results: This is the character set for the server to convert the result data to. It is sent to the client only after conversion.

The entire process:
-The client (such as a PHP program) sends a query;
-When the server receives the query, it converts the query string from character_set_client to character_set_connection, and then executes the converted query;
-The server converts the result data to the character_set_results Character Set and sends it back to the client.

You can use the following two commands to view the character set and sorting method settings of the system:
Mysql> show variables like 'character _ SET _ % ';
Mysql> show variables like 'collation _ % ';

The default Character Set of MySQL is Latin1, which is used to connect Latin1-_ swedish_ci. You have a little understanding about the test,
The query we send through PHP is generally utf8, GBK, gb2312, and its default value is Latin1, so you can use phpMyAdmin to view or
Directly into the database to view the data, are some garbled. (Most of them can be accessed and stored on the webpage .) PhpMyAdmin
For MySQL 4.1 or earlier, built-in libraries are used to convert character sets to support multi-language versions. For MySQL or later versions
All are handed over to MySQL. Therefore, if character sets do not match during the conversion of the above several layers, garbled characters are easy to understand.
After talking about it for a long time, you only need to set the above variables in the same way, whether it is directly viewed in the database or in phpMyAdmin.
No Chinese garbled characters are displayed.

If your system and PHP webpage are UTF-8 encoded, you can change the MySQL encoding from the default Latin1 to utf8.
Edit the MySQL configuration file/etc/My. CNF and set default-character-set = utf8 in [mysqld.

From
Http://www.syict.com/html/2/2-10322.html

Part

A. MySQL must have a character set, which can be specified by adding parameters at startup, during compilation, or in the configuration file.
The MySQL Server character set is used as the default value at the database level. When creating a database, you can specify the character set. If not,
Use the server character set. Similarly, when creating a table, you can specify the character set at the table level. If not, use the character set of the database.
As the character set of the table. When creating a column, you can specify the character set of a column. If not specified, you can use the character set of the table. Generally,
You only need to set server-level character sets, other database-level, table-level, and column-level character sets.
Because utf8 is the most extensive character set, we usually set the MySQL server-level character set to utf8!

B. html is a text file. When storing HTML files, an encoding is required. In addition, HTML syntax is also used in HTML files,
Specifies the encoding used by the file. If no encoding is specified for an HTML file, the browser automatically identifies the file encoding.
If encoding is specified in HTML, the browser uses the encoding specified in HTML. Generally, the charset and HTML files specified by the HTML file
Their encoding is consistent, but there are also inconsistencies. If they are inconsistent, it will lead to garbled web pages (garbled here, only related to text files,
It has nothing to do with the database .) Using specialized webpage editing tools (such as dreamwave), files are automatically encoded Based on the charset value in the webpage.

C. PHP + MySQL Character Set Problems

To ensure no garbled characters (including not garbled characters displayed in the browser), the three codes must be unified:
First, the webpage code,
The second is the encoding specified in HTML (that is, the encoding used for Browser display ),
Third, PHP tells MySQL code
(Including character_set_client and character_set_results ).

The first and second codes are usually the same if you use an editor such as DW, but the webpages written in Notepad may be inconsistent. Shenyang Institute of Chemical Technology. Student station, E]/& C o1a t "]
The third encoding must be manually notified to MySQL. In this step, you can use mysql_query ("set names characterx") in PHP.

Summary:
1. Use utf8 for database storage whenever possible (modify/etc/My. CNF and add default-character-set = utf8 in the [mysqld] segment)
(Convert existing databases to utf8 format first)

2. Before the PHP program queries the database, execute mysql_query ("set names XXXX ");
XXXX indicates the code of Your webpage (charset = XXXX). If charset = utf8 In the webpage, XXXX = utf8,
If charset = gb2312 is displayed on the webpage, XXXX = gb2312. Almost all web programs have a public section connecting to the database.Code


,
Put it in a file. In this file, add mysql_query ("set names.

3. phpMyAdmin does not need to be modified.

4. Note that to ensure the actual encoding of the webpage (encoding in the Windows save dialog box) and its declared encoding (charset = ?) Yes. use tools such as DW for Web pages.