Resolve MySQL table character set GBK, column character set Latin1,python query garbled problem

Recently encountered an abnormal egg pain in the company, MySQL database, database and table of the character set are ' GBK ', but the character set of the column is ' Latin1 ', so the egg hurt things appear.

Regardless of my connection string ' charset ' set to ' gbk ', ' utf8 ', ' latin1 ' any of the data in the query table is garbled, the query with the following code is still useless:

SET NAMES latin1 in the replacement of various PY link library, then crazy Google and asked the big God, finally found a solution to the following ideas: 1, by Hex (column) in the column of data 2 into the 16 binary string representation, and then returned to the PY 2, py through 2 times decode operation, the hex string into Unicode encoding 3, the last one encode, the results into UTF8 responseThe pseudo code is as follows:

defHex2char (hexstring): Output= Hexstring.decode ('Hex'). Decode ('GBK'). Encode ('UTF8')        returnOutput...sqlstr="SELECT Acc_name,level_n,hex (char_name) from roles where acc_name= ' Noc20 '"Cur.execute (SQLSTR) forCinchCur:char_name= Hex2char (c[2])...

Finally analyze why before, set charset= ' GBK ', and then the query results directly decode (' GBK ') cannot operate, because the database character set and the table's character set are ' GBK ', so the database link here we must set as ' GBK ', But because we need to get the column name character set by the pit Father's setting to ' latin1 ', so the query returned by the string is actually ' latin1 ' encoded, essentially ' GBK ' of the string, of course, using any kind of decode can not be converted correctly, only the string to 2 binary representation Can be lossless from the database read data, and ' hex ' operation happens to help us to read the data from the database, and then smoothly Cheng Zhang 2 times decode, finally get the string we need, and finally again condemned which pit father designed such a database.

Troubleshoot table character set GBK in MySQL, column character set Latin1,python query garbled problem