Some of my friends often encounter the problem that I entered the correct Chinese. Why is there a garbled code in my queries on another computer? In fact, this is a problem that occurs when the database is converting character sets. This article introduces the solution. For more information, see
Some of my friends often encounter the problem that I entered the correct Chinese. Why is there a garbled code in my queries on another computer? In fact, this is a problem that occurs when the database is converting character sets. This article introduces the solution. For more information, see
Problem description:
Some of my friends often encounter the problem that I entered the correct Chinese. Why is there a garbled code in my queries on another computer? In fact, this problem occurs when the database performs Character Set conversion,
The following describes the specific situation through testing:
1. Environment
Oracle Database character set:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'nls _ CHARACTERSET ';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_CHARACTERSET ZHS16GBK Character set
The customer character set of the Oracle database:
In the Registry: NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK for example:
Character Set of the operating system where Oracle is located:
Microsoft Windows [version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C: \ Users \ Andy> chcp
Code Page: 936
Representation: Chinese-Simplified Chinese (GB2312)
2. Test
The character set is as follows:
Oracle Database Character Set: ZHS16GBK
Character Set of the Oracle database client: ZHS16GBK
Operating System Character Set: Chinese-Simplified Chinese (GB2312)
Input test data:
SQL> INSERT INTO TAB_INDX
2 values (1, 'character set test', 'Chinese Input test', sysdate );
1 row inserted
If the character set is not modified, the actual test data is:
SQL> select * from tab_indx where tid = 1;
TID TNAME TDESC SYSDT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Chinese Character Input Character Set Test Chinese Input Test 2012/12/30
Display normal,
Now I have changed the customer service character set to UTF8.
And the Registry: NLS_LANG = SIMPLIFIED CHINESE_CHINA.UTF8
The current character set is as follows:
Oracle Database Character Set: ZHS16GBK
Character Set of the Oracle database client: UTF8
Operating System Character Set: Chinese-Simplified Chinese (GB2312)
Now, query the entered data:
SQL> select tname, tdesc from tab_indx;
TNAME TDESC
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------
It is found that the queried data is no longer normal because these Chinese characters are stored in the ZHS16GBK encoding format. However, after you query the data, convert it according to the Oracle Customer Service Code (UTF8, and converted to the UTF8 encoding format, but the operating system is simplified Chinese (GB2312), so the operating system regards the UTF8 encoding format data as simplified Chinese (GB2312) when the encoding format data is displayed, garbled characters are displayed,
Now I insert another data entry:
SQL> INSERT INTO TAB_INDX
2 values (1, 'utf8 Chinese Character Input Character Set test', 'Chinese Input test', sysdate );
1 row inserted
Query again:
SQL> select tname, tdesc from tab_indx;
TNAME TDESC
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------
The newly inserted data is also garbled, but the garbled code is different from the value just now ?? Why?
Because the entered Chinese characters are in the simplified Chinese (GB2312) encoding format, when the Oracle database is passed to the database according to the client encoding format, the Oracle database found that the Oracle database client is in the UTF8 encoding format, unlike the database encoding format (ZHS16GBK), it performs Character Set conversion, UTF8 --> ZHS16GBK, so the data in the simplified Chinese (GB2312) encoding format is treated as UTF8, an error has occurred when converting the data into the ZHS16GBK encoding format. If it is queried, it will not work if it is converted back.
(This is the case when the character set of the Oracle database needs to be tested and modified.) Here we are modifying the character set of the Oracle database;
In the Registry: NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK
The current character set is as follows::
Oracle Database Character Set: ZHS16GBK
Character Set of the Oracle database client: ZHS16GBK
Operating System Character Set: Chinese-Simplified Chinese (GB2312)
Query again:
SQL> select tname, tdesc from tab_indx;
TNAME TDESC
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------
Chinese Character Input Character Set Test Chinese Input Test
UTF8 ????????????? Chinese Input Test
It is found that the Chinese character entered at the beginning is normal, but the Chinese character entered for the second time has changed. The Chinese character entered at the beginning is different from the garbled characters at the same time ????
Although the encoding format is not converted here, the last time the data is stored, it is already an incorrect encoding format, so it is definitely not displayed correctly.
In fact, there are several cases of testing. Due to restrictions in the local environment, you can try other cases of testing. For example, the character set of the database is UTF8, and then the character set of the customer service is changed, what is the impact on the input and output of Chinese characters?
Based on the above test and my own analysis, the following is a summary:
1. The data queried by the database is the result of the combination of the Oracle database character set, Oracle client character set, and operating system character set.
2. Both Oracle storage and query data are converted using the character set of the Oracle database and the character set of the Oracle customer service. The displayed data is determined based on the character set of the operating system.
3. To avoid garbled characters, you must set the character set of the Oracle client to the same as that of the operating system.
