php mssql extension SQL query Chinese field name solution
- 2020-05-26 08:02:28
- OfStack
1. The question is:
The database is MS SQLServer2000, the data of a table in SQLServer2000 should be imported into MySQL5, in which the fields of SQLServer2000 table are named in simplified Chinese (it is strongly recommended not to use Chinese as the field name). The operation is to query the records of SQLServer and insert them into MySQL. The scripting language of choice is PHP, and PHP opens the MSSQL and MySQL extensions, which are easy problems for both database operations.
The problem is that the field name of the table in SQLServer is in Chinese, the written query statement is tested in SQLServe and returned with records, and the extended query with PHP's MSSQL is error reporting.
I checked the information on the Internet, but there is not much information on the Internet. Many netizens think that the MSSQL extension of PHP does not support SQL in Chinese. After checking 1 data, MSSQL of PHP supports the Chinese language in SQL. Most of the error reporting problems are caused by coding problems. The coding database and the coding are not unified with the query statement coding. The Chinese part of the query statement in SQLServer becomes garbled, resulting in the query failure.
2. Solutions:
After knowing the reason, I will analyze and solve the problem and confirm that the coding is not unified. The solutions are as follows:
1. Confirm the code of SQLServer database. My data code is GBK.
2. Confirm the encoding of the current PHP script file. My encoding is UTF-8.
3. Convert the encoding of SQL query statement.
Supplement: some net friend mentioned to PHP script file code into and database code 1, in fact this step 1 don't have to, as long as you confirm your SQL statements and database code 1 can, don't need to transform is proposed, the reason is that if you PHP script file contains other PHP script, that is for all include or require script file encoding conversion, otherwise PHP script code no unification 1 is very easy to get wrong, if a lot of related files, this also is a very troublesome problems and make things complicated.
3. Solution:
Write a conversion function that encodes the SQL statement before the SQL operation. Post my sample code below:
The database is MS SQLServer2000, the data of a table in SQLServer2000 should be imported into MySQL5, in which the fields of SQLServer2000 table are named in simplified Chinese (it is strongly recommended not to use Chinese as the field name). The operation is to query the records of SQLServer and insert them into MySQL. The scripting language of choice is PHP, and PHP opens the MSSQL and MySQL extensions, which are easy problems for both database operations.
The problem is that the field name of the table in SQLServer is in Chinese, the written query statement is tested in SQLServe and returned with records, and the extended query with PHP's MSSQL is error reporting.
I checked the information on the Internet, but there is not much information on the Internet. Many netizens think that the MSSQL extension of PHP does not support SQL in Chinese. After checking 1 data, MSSQL of PHP supports the Chinese language in SQL. Most of the error reporting problems are caused by coding problems. The coding database and the coding are not unified with the query statement coding. The Chinese part of the query statement in SQLServer becomes garbled, resulting in the query failure.
2. Solutions:
After knowing the reason, I will analyze and solve the problem and confirm that the coding is not unified. The solutions are as follows:
1. Confirm the code of SQLServer database. My data code is GBK.
2. Confirm the encoding of the current PHP script file. My encoding is UTF-8.
3. Convert the encoding of SQL query statement.
Supplement: some net friend mentioned to PHP script file code into and database code 1, in fact this step 1 don't have to, as long as you confirm your SQL statements and database code 1 can, don't need to transform is proposed, the reason is that if you PHP script file contains other PHP script, that is for all include or require script file encoding conversion, otherwise PHP script code no unification 1 is very easy to get wrong, if a lot of related files, this also is a very troublesome problems and make things complicated.
3. Solution:
Write a conversion function that encodes the SQL statement before the SQL operation. Post my sample code below:
// Encoding conversion function
function utf8togb($s) {
return iconv('utf-8', 'gbk//IGNORE', $s); // IGNORE The argument is ignored when a non-converted character is encountered
}
// It is recommended to replace all Chinese fields with English aliases for the convenience of the following operations and encoding conversion
$sql="SELECT [id], [ The column ] as typeid, [ To the chase ] as title, [ The author ] as author, convert(text, [ The body of the ]) as body FROM [ The article table ];";
$sql = utf8togb($sql);