Solve the Problem of garbled characters in mysql table Character Set gbk, column Character Set Latin1, and python query, mysqllatin1

Solve the Problem of garbled characters in mysql table Character Set gbk, column Character Set Latin1, and python query, mysqllatin1

Recently, I encountered an abnormal problem in the company. In mysql databases, the character sets of databases and tables are both 'gbk', but the column character sets are 'latin1.

No matter whether the 'charset' of the connection string is set to 'gbk', 'utf8', or any of the 'latin1', the Chinese data in the queried table is garbled, adding the following code to the query does not help:

Set names latin1 is replacing various py link libraries, and after crazy google and asking various great gods, the solution is finally found as follows: 1. Use hex (column) to convert the data in the column into a hex string, and then return it to The py 2. py converts the hex string to Unicode encoding through two decode operations. 3. Perform the last encode to convert the result into utf8 response.The pseudocode is as follows:

def hex2char(hexString):        output = hexString.decode('hex').decode('gbk').encode('utf8')        return output...sqlStr = "SELECT acc_name,level_n,hex(char_name)  FROM roles where acc_name='noc20'"cur.execute(sqlStr)for c in cur:    char_name = hex2char(c[2])...

Finally, let's analyze why charset = 'gbk' was set, and then decode ('gbk') for the query results directly failed to operate, because both the database character set and table character set are 'gbk', we must set the database link to 'gbk' here ', however, because the character set of the column name we need to obtain is set to 'latin1', the string returned by the query is actually encoded in 'latin1', which is essentially a string of 'gbk, of course, any type of decode cannot be correctly converted. data can be read from the database without loss only when the string is converted to a binary representation, the 'hex' operation helps us read data from the database without interrupting, and then proceed to decode twice and finally obtain the required string, finally, I once again condemned the hacker who designed such a database.