MySQL Chinese garbled problem, phpmyadmin operation solution

MySQL Chinese garbled problem has been migrated every time the database will be resolved from the beginning, because the database is not how to move after the building, has not been a matter, anyway, trouble once. Recently the server encountered a problem, re-installed a few times, the results of each time to reconfigure the problem, simply summed up.

First of all, the fundamental problem of Chinese garbled is the coding problem: we input the Chinese into the database and then removed from the database explicitly divided into several processes in the browser, these processes require that each process of encoding is to support the Chinese language, and if the previous two processes if the encoding method is not the same, must have transcoding means. For example, you have written a Chinese language in the local code using GBK, or. sql script, upload the server after the execution, if your MySQL default to UTF8 code to read your SQL statement, then the Chinese is garbled. (not tested, it is not GBK and UTF8 are compatible or MySQL by default will be transcoded by Unicode.) But that's roughly what it means. It can be imagined that it is tedious to check the coding problem between all the processes, so the best solution is to choose one encoding and then use that code in all the processes so that you don't have to worry about the encoding conversion problem.

Then talk about the code rules in MySQL, MySQL in each database, each database in each table, each table in each of the fields have a set of variables about the encoding, (remember that it is possible to use SQL statement in MySQL: Show variables like "%char% ") to find out. These variables are prioritized from small to large. That is, if you set the default encoding of a TestDB database to be Latin, then after you create a new table (without declaring the encoding), all the fields of the table are encoded Latin, but you can create a new table with another encoding (for example, UTF8). The practice is to explicitly declare what encoding to use when building a table. This makes it possible to have both Latin encoded and UTF8 encoded tables in a database. The same rules can be applied to fields in tables and tables.

The first is here to delay for a long time, because they have clearly been the database encoding method to UTF8, but still show garbled, always thought is where the code is not unified, checked several times. The problem is that this table was created before modifying the default encoding of the database, so the default encoding for this table is Latin, and the fields in this table are also default to Latin. So in addition to modifying the default encoding of the database, you need to modify the default encoding for the table, and then you need to modify the default encoding for the field. Of course, if you have only one table in your database that needs Chinese, then you just have to modify its default encoding when creating the table. But it is useless to modify the default encoding of the table if you set up the table first, because the fields in the table are encoded according to the time the table was established. So for a table that has already been built, it is necessary to modify the encoding of the field to modify the default encoding of the table, as well as to modify the encoding of the specific field.

Here I use phpMyAdmin as an example to illustrate how the table that has been built to support Chinese:

In phpMyAdmin, "default ordering" is the default encoding meaning, I do not know why so to say ...

First we see what the default code is MySQL, about the code has several variables, each variable is responsible for different functions, the specific introduction click on the question mark can have the official MySQL explanation, or to check the MySQL manual for you.

In the case of this default encoding we create a new database test:

We don't choose the encoding method, which is "collation".

Then create a new table:

Also does not choose the encoding method

Then insert a record with Chinese in it:

It will appear garbled:

The reason is that the name field is encoded by default in this case:

I don't know why it's changed from Latin1 to Cp1250_bin. Maybe there are rules in other settings in MySQL ...

Let's get rid of this code:

Insert a second record in Chinese:

can see solved.

Now let's change our mind: Get rid of the default code of the test database:

Re-establish a table testtable2:

You can see that the Testtable2 code has become UTF8.

Also insert a Chinese record, you can see the problem also solved:

To summarize: Although many database management tools now support edge development, modify the database. But when you create a new database, you should think about the nature of the database, the dependencies between tables, and so on. Otherwise, it is inefficient to debug the database in a way that debugs the program.

MySQL Chinese garbled problem, phpmyadmin operation solution