Encoding problems and solutions when mysql associates two tables

When Mysql associates two tables, an error message is generated: Illegal mix of collations

1. First use the tool to change the encoding method of the database and two tables

2. This step is very important and requires changing the encoding method of the field.

ALTER TABLE `table name` CHANGE `dev_chancode` `field` VARCHAR(32) CHARACTER SET gbk NOT NULL;

Summary: When creating a table, you must pay attention to the unified encoding method, otherwise it will be very troublesome to deal with it later.

How to solve the MySQL table encoding conversion problem

1. Export the table structure of the data table to be exported (you can use Phpmyadmin, mysqldump, etc., it's very simple and I won't explain it in detail), then change the CHARSET=latin1 of the exported create table statement to CHARSET=utf8, execute the create table statement in the target library newdbname to build the table structure, and then start exporting and importing data. Order:
2. ./mysqldump -d DB_Dig > /usr/local/tmp/tables.sql
3. Command line: Enter the mysql command line, mysql -hlocalhost -uroot -p*** dbname
4. Execute SQL select * from tbname into outfile '/usr/local/tbname.sql';
5. Convert tbname.sql to UTF-8 format. It is recommended to use UltraEditor. You can directly use the editor's 'Convert->ASCII to UTF-8 (Unicode Editing)', or save the file as UTF-8 (without BOM) format.
6. Execute the statement set character_set_database=utf8 in the mysql command line; Note: Set the mysql environment variable so that mysql will interpret the content of the sql file in utf8 format when reading the sql file in the next step
7. Execute the statement load data infile 'tbname.sql' into table newdbname.tbname in the mysql command line;

The above is all the knowledge points introduced this time. Thank you for your learning and support for 123WORDPRESS.COM.