如何使用CodeIgniter在SQL Server数据库中存储多字节字符 [英] How to store multi byte characters in SQL Server database using CodeIgniter

问题描述

我正在将MS SQL Server和带有Active Record的CodeIgniter 2用于正在处理的项目，而我偶然发现了这个问题：

I'm using MS SQL Server and CodeIgniter 2 with Active Record for a project I'm working on, and I just stumbled upon this issue:

当我提交包含中文或印地语字符的表格，然后将其存储在表格中，当我查看表格时，得到的只是问号。如果我尝试使用英语或希腊字符，一切似乎都可以正常工作。

When I submit a form that contains Chinese or Hindi characters, I store it in a table, and when I view it all I get are question marks. If I try English or Greek characters, everything seems to work fine.

我认为这与我编写的PHP有关，是因为如果我直接在SQL Server Management Studio中复制粘贴中文文本，所有值都可以完美存储并显示在SQL Studio和Web应用程序上。

The reason I believe this is something to do with the PHP I'm writing, is because if I copy-paste the chinese text directly in SQL Server Management Studio, all values are stored and displayed perfectly, both on the SQL Studio, and the web application.

这些是我使用的db设置：

These are the db settings I'm using:

$db['local']['dbdriver'] = 'sqlsrv';
$db['local']['dbprefix'] = '';
$db['local']['pconnect'] = FALSE;
$db['local']['db_debug'] = TRUE;
$db['local']['cache_on'] = FALSE;
$db['local']['cachedir'] = '';
$db['local']['char_set'] = 'utf8';
$db['local']['dbcollat'] = 'utf8_general_ci';
$db['local']['swap_pre'] = '';
$db['local']['autoinit'] = TRUE;
$db['local']['stricton'] = FALSE;

这是我目前正在测试的表的结构：

This is the structure of the table I'm testing on right now:

CREATE TABLE [dbo].[languages](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [language] [nvarchar](1024) NULL,
    [language_local] [nvarchar](1024) NULL,
    [lang_code] [nvarchar](100) NULL,
    [core] [bit] NULL,
 CONSTRAINT [PK_languages] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,         ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

这是我在config.php中的字符集编码

And this is my charset encoding in config.php

$config['charset'] = 'utf-8';

新的故障排除数据

我尝试通过表单保存以下字符串：Iñtërnâtiônàlizætiøn

I tried to save the following string through my form: Iñtërnâtiônàlizætiøn

CodeIgniter对此错误进行了答复：

CodeIgniter replied with this error:

An error occurred translating the query string to UTF-16: No mapping for the Unicode character exists in the target multi-byte code page. .

当我尝试存储汉字时不会出现
提前谢谢： ）

This doesn't appear when I try to store Chinese characters Thank you in advance :)

推荐答案

看起来像这样的答案引起了很多关注，我为不发布实际解决方案感到难过解决我的问题...我想取消选择我多年以前选择的答案是不好的礼节，所以我暂时不会。

无需更改设置。问题与查询有关，不幸的是CodeIgniter不支持开箱即用的正确查询格式。

No changes needed to be done to the settings. The problem is query related, and unfortunately CodeIgniter doesn't support the proper query format out of the box.

因此，当您想在表中插入多字节字符时，必须在字符串前加上字符 N 。

So when you want to insert multibyte characters into your table, you have to prepend the character N before your string.

因此在上面的示例中，查询应如下所示：工作顺序

So in my example above the query should look like this in order to work

INSERT INTO test_table (title) VALUES (N'Iñtërnâtiônàlizætiøn')

不，CI当前没有为您提供内置方法来执行此操作。计划将其添加到CI4中，但在此之前，这里有您的黑客

No, CI doesn't currently give you a built in way to do this. It is planed to be added in on CI4, but until then here is a hack for you

这篇关于如何使用CodeIgniter在SQL Server数据库中存储多字节字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！