MySQL database garbled-Linux garbled problem 1

MySQL database garbled-The garbled problem in Linux is very frustrating. Next I will record the solution to the mysql garbled problem in linux. Mysql garbled characters in linux I. The default character set for operating mysql is latin1, but most of our programs use utf8, so we need to modify the character set of mysql. 1) view the default encoding

show variables like 'character%';+--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 

 

The default encoding in Mysql is latin1 2) view the default sorting method

show variables like 'collation_%';+----------------------+-----------------+ | Variable_name        | Value           | +----------------------+-----------------+ | collation_connection | latin1_swedish_ci | | collation_database   | latin1_swedish_ci | | collation_server     | latin1_swedish_ci | +----------------------+-----------------+ 

 

3) modify the default Character Set modification file/etc/my. add the following statement default-character-set = utf8 under [client] to add default-character-set = utf8 init_connect = 'set NAMES utf8' under [mysqld] # SET after the UTF8 encoding is used to connect to mysql, restart the mysql service. 4) After logging on to mysql with another character encoding method, enter the following content:

mysql> SET character_set_client = utf8 ; mysql> SET character_set_connection = utf8 ; mysql> SET character_set_database = utf8 ; mysql> SET character_set_results = utf8 ; mysql> SET character_set_server = utf8 ; mysql> SET collation_connection = utf8 ; mysql> SET collation_database = utf8 ; mysql> SET collation_server = utf8 ; 

 

5) Review

show variables like 'character%';+--------------------------+---------------------------------+ | Variable_name            | Value                           | +--------------------------+---------------------------------+ | character_set_client     | utf8                            | | character_set_connection | utf8                            | | character_set_database   | utf8                            | | character_set_filesystem | binary                          | | character_set_results    | utf8                            | | character_set_server     | utf8                            | | character_set_system     | utf8                            | | character_sets_dir       |/usr/share/mysql/charsets/| +--------------------------+---------------------------------+show variables like 'collation_%';+----------------------+-----------------+ | Variable_name        | Value           | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database   | utf8_general_ci | | collation_server     | utf8_general_ci | +----------------------+-----------------+ 

 

6) when using JDBC to connect to mysql, you need to change the connection string to the following method: jdbc: mysql: // localhost/mysql? UseUnicode = true & characterEncoding = UTF-8 utf8 connection 2. Concept character-set-server/default-character-set: server character set, which is used by default. Character-set-database: database character set. Character-set-table: character set of the database table. Character-set-client: character set of the client. Default Character Set of the client. When a client sends a request to the server, the request is encoded in this character set. Character-set-results: The result character set. When the server returns results or information to the client, the results are encoded in this character set. Unified Character set encoding statement: set names utf8 3. Import and export to prevent garbled characters 1. export the database using the command (enter in cmd) mysqldump-u username-p password-h server address database name -- default-character-set = utf8 -- hex-blob> export the database. SQL -- default-character-set = utf8 indicates that the data is exported using urf8 encoding -- hex-blob indicates that the database is exported in hexadecimal format. 2. import the database using the command (enter in cmd) mysql-u username-p password-h server address database name -- default-character-set = utf8 -- hex-blob <export the database. in addition to using the preceding command to import a database, SQL can also log on to mysql and use the source command to import mysql> source to export the database. SQL Note: blob fields store binary data, Therefore, it does not have character set encoding. In Windows and linux, garbled characters still exist. Therefore, we recommend that you change the blob type to the text type to avoid garbled characters. Blob data is converted to the text type. You can write a simple code to read data from blob and save it as a string to the text field.