Setting character encoding in Mysql can solve some common problems. For example, if an error occurs when using different character sets, the following describes how to set character encoding in Mysql to solve this problem.
The error is that there are two character sets in your result set.
For example, if you perform a joint query on two tables, the character set of one table is latin1 and the other is utf8,
In this way, there are two character sets in your result set. mysql will report an error.
It is also true that different fields in a table use different character sets.
Use show create table table_name; to see the specific character set settings.
I checked the help manual, saying that the user's character set is not set. The default is utf8, And it is converted to the latin1 or gb2312 character set.
Solution:
Convert different character sets into a unified character set. The following describes how to set character encoding for Mysql.
- After an upgrade to MySQL 4.1, the statement fails:
-
- mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
-
- ERROR 1267 (HY000): Illegal mix of collations
-
- (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)
-
- for operation 'substr_index'
-
- The reason this occurs is that usernames are stored using UTF8 (see section 11.6 UTF8 for Metadata). As a result, the USER() function and the literal string '@' have different character sets (and thus different collations):
-
- mysql> SELECT COLLATION(USER()), COLLATION('@');
-
- +-------------------+-------------------+
-
- | COLLATION(USER()) | COLLATION('@') |
-
- +-------------------+-------------------+
-
- | utf8_general_ci | latin1_swedish_ci |
-
- +-------------------+-------------------+
-
- One way to deal with this is to tell MySQL to interpret the literal string as utf8:
-
- mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
-
- +------------------------------------+
-
- | SUBSTRING_INDEX(USER(),_utf8'@',1) |
-
- +------------------------------------+
-
- | root |
-
- +------------------------------------+
-
- Another way is to change the connection character set and collation to utf8. You can do that with SET NAMES 'utf8' or by setting the character_set_connection and collation_connection system variables directly.
You can use MySQL Version> 4.12 for table encoding and conversion)
- ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
Earlier versions can be used:
- ALTER TABLE tbl_name CHARACTER SET charset_name;
Statement for modifying fields in mysql
How to delete multiple tables in MySQL
How MySQL connects to remote SQL
MySQL cluster introduction and Configuration
Special notes for MySQL database