MySQL database table collation inconsistency results in a query that does not work on the index

MySQL database table collation inconsistency results in a query that does not work on the index

Table is more descriptive: Add the Worktask table in the MySQL database to the Ishaspic field.

How to Do: (1) Database Worktask table New Add whether there is a picture field ishaspic; new field, error

[SQL] ALTER TABLE worktask add Ishaspic Int (ten) Null;
[ERR] 1034-incorrect key file for table ' Worktask '; Try to repair it

Solution: Create a new Worktask table, add the Ishaspic field, and insert the data from the original Worktask table into the new table.

(2) Production environment deployment change code, after the application started, a period of time the background error

Org.hibernate.exception.GenericJDBCException:Could Not open connection

Java.sql.SQLException:An attempt by a client to checkout a Connection have timed out.

(3) Monitoring the database service, found that the query SQL suddenly slowed down, the database server CPU Resource usage is very high.

(4) Change fallback, error still exists.

The original application can run normally, and now can not run properly, monitoring the database, found that the SQL query execution time suddenly slowed down, the execution of SQL alone, the discovery is also very slow to reach the hundred second level.

Problem locating: When you create a new Worktask table, you change the collation of the table so that it does not have the same collation as the table, causing the table to query the SQL statement and the index does not work.

Solution: (1) The query SQL is split, and (2) the collation of the data table is adjusted to ensure that the collation of the table data table is consistent.

*************************************************************************************

The collation in MySQL. The character set and collation are often selected when creating a new MySQL database or table. The character set used by the database everybody knows what's going on, what sort of rules is that?

Collation: Refers to a comparison rule for different characters under a specified character set. Its characteristics include the following points:

1, two different character sets cannot have the same collation

2, two character sets have a default collation

3, there are some common naming rules. such as the _ci end indicates case insensitive (caseinsensitive), _cs is case sensitive, and _bin indicates binary comparison (binary).

In the MySQL database, you can use show collation to see the various sorting rules supported, I use MySQL 5.6.12-log version of the database, support 219 kinds of collation.

MySQL database table collation inconsistency results in a query that does not work on the index