我有一个由表job
,steps
和company
组成的MySQL数据库。
公司表:
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id | varchar(45) | NO | PRI | NULL | |
| prefecture_id | varchar(2) | NO | PRI | NULL | |
| industry_id | varchar(2) | NO | PRI | NULL | |
| offset | int(11) | YES | | NULL | |
| pid | varchar(255) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| tel | varchar(50) | YES | | NULL | |
| fax | varchar(50) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| address_coordinate | varchar(255) | YES | | NULL | |
| url | varchar(511) | YES | | NULL | |
| image_url | varchar(511) | YES | | NULL | |
| flexible_1 | varchar(511) | YES | | NULL | |
| flexible_2 | varchar(511) | YES | | NULL | |
| flexible_3 | varchar(511) | YES | | NULL | |
| flexible_4 | varchar(511) | YES | | NULL | |
| flexible_5 | varchar(511) | YES | | NULL | |
| flexible_6 | varchar(511) | YES | | NULL | |
| flexible_7 | varchar(511) | YES | | NULL | |
| flexible_8 | varchar(511) | YES | | NULL | |
| flexible_9 | varchar(511) | YES | | NULL | |
| flexible_10 | varchar(511) | YES | | NULL | |
| sequence | varchar(511) | YES | | NULL | |
| del_flg | char(1) | YES | | NULL | |
| create_date | datetime | YES | | NULL | |
| create_user | varchar(255) | YES | | NULL | |
| update_date | datetime | YES | | NULL | |
| update_user | varchar(255) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+-------+
我正在尝试保存来自此代码的日语文本的数据;
public function getDetail($prefectureId,$pid,$offset,$industryId,$companyId,$entryName,$priority,$searchNum)
{
$result = $this->itpApi->getCompany($prefectureId,$pid,$offset,$industryId,$companyId,$entryName,$priority,$searchNum);
$itpCompanyDetail = $this->itpApi->getReturnArray();
$this->db->insert('company',$itpCompanyDetail);
}
这是我获取数据的代码;
protected function execApihtmlParser($companyId,$opt,$prefectureId,$pid,$offset,$industryId,$slp=1) {
$curl = curl_init();
sleep($slp);
curl_setopt_array($curl, $opt);
$response = curl_exec($curl);
$err = curl_error($curl);
curl_close($curl);
if ($err) {
$this->errMsg = "cURL Error #:" . $err;
return false;
} else {
$response = mb_convert_encoding($response, 'UTF8', 'ASCII,JIS,UTF-8,EUC-JP,SJIS-WIN');
$xml = simplexml_load_string($response);
$html = new simple_html_dom();
$html->load($xml->retdata);
$item = array();
foreach($html->find('body') as $home) {
//populate all items
$item['id'] = $companyId;
$item['prefecture_id'] = $prefectureId;
$item['industry_id'] = $industryId;
$item['offset'] = $offset;
$item['pid'] = $pid;
$item['name'] = str_replace("rn",'',trim($home->find('div[id="home"] div[id="topinfo"] h1[id="basetop"]', 0)->plaintext));
$item['email'] = preg_replace("/s+/u",'',trim($home->find('div[id="home"] div[id="mainblock"] div[class="txtblock"]', 8)->plaintext));
$item['tel'] = preg_replace("/s+/u",'',trim($home->find('div[id="home"] div[id="topinfo"] p', 2)->plaintext));
$item['fax'] = preg_replace("/s+/u",'',trim($home->find('div[id="home"] div[id="mainblock"] div[class="txtblock"] p', 3)->plaintext));
$item['address'] = str_replace("rn",'',trim($home->find('div[id="home"] div[id="mainblock"] div[class="txtblock"] p', 4)->plaintext));
$item['address_coordinate'] = str_replace("rn",'',trim($home->find('div[id="home"] div[id="mainblock"] div[class="txtblock"] p', 4)->plaintext));
$item['url'] = preg_replace("/s+/u",'',trim($home->find('div[id="mainblock"] div[class="txtblock"]', 7)->plaintext));
$item['image_url'] = str_replace("rn",'',trim($home->find('div[id="home"] div[id="shopintro"] p img', 0)->src));
$item['flexible_1'] = preg_replace("/s+/u",'',trim($home->find('div[id="home"] div[id="mainblock"] div[class="txtblock"] p', 1)->plaintext));
$item['flexible_2'] = preg_replace("/s+/u",'',trim($home->find('div[id="home"] div[id="mainblock"] div[class="txtblock"] p', 5)->plaintext));
$item['flexible_3'] = preg_replace("/s+/u",'',trim($home->find('div[id="home"] div[id="mainblock"] div[class="txtblock"] p', 6)->plaintext));
$item['flexible_4'] = preg_replace("/s+/u",'',trim($home->find('div[id="home"] div[id="mainblock"] div[class="txtblock"]', 11)->plaintext));
$item['flexible_5'] = preg_replace("/s+/u",'',trim($home->find('div[id="category"] div[id="mainblock"] div[class="txtblock"]', 0)->plaintext));
$item['flexible_6'] = preg_replace("/s+/u",'',trim($home->find('div[id="category"] div[id="mainblock"] div[class="txtblock"]', 1)->plaintext));
$item['flexible_7'] = preg_replace("/s+/u",'',trim($home->find('div[id="category"] div[id="mainblock"] div[class="txtblock"]', 2)->plaintext));
$item['flexible_8'] = preg_replace("/s+/u",'',trim($home->find('div[id="category"] div[id="mainblock"] div[class="txtblock"]', 3)->plaintext));
$item['flexible_9'] = preg_replace("/s+/u",'',trim($home->find('div[id="category"] div[id="mainblock"] div[class="txtblock"]', 4)->plaintext));
$item['flexible_10'] = preg_replace("/s+/u",'',trim($home->find('div[id="category"] div[id="mainblock"] div[class="txtblock"] p', 5)->plaintext));
}
$items = array();
$items = $item;
// When parsing fails, processing ends
if ($items === NULL) {
$this->errMsg = "Parse error: syntax error.";
return false;
}
$this->returnArray = $items;
return true;
}
}
我检查数据库表company
后,它具有'??????'问号插入。这样;
| KN0100060500041981 | 01 | 1 | 400 | 11178 | ??????? | | 011-512-5351 | 064-0807??????????????????? | ????? | ????? | ?????????????? | | ???????? | ?????? | ????? | ??????????????? | ???????? | ??????? | ???????? | ??????????? | ??????? | ??????? | NULL | NULL | NULL | NULL | NULL | NULL |
注意:我刚刚找到了。
我通过命令行检查了数据库设置;
使用SHOW CREATE TABLE company;
这是结果;
| company | CREATE TABLE `company` (
`id` varchar(45) CHARACTER SET latin1 NOT NULL,
`prefecture_id` varchar(2) CHARACTER SET latin1 NOT NULL,
`industry_id` varchar(2) CHARACTER SET latin1 NOT NULL,
`offset` int(11) DEFAULT NULL,
`pid` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`email` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`tel` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`fax` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`address` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`address_coordinate` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`url` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`image_url` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`flexible_1` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`flexible_2` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`flexible_3` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`flexible_4` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`flexible_5` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`flexible_6` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`flexible_7` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`flexible_8` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`flexible_9` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`flexible_10` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`sequence` varchar(511) CHARACTER SET latin1 DEFAULT NULL,
`del_flg` char(1) CHARACTER SET latin1 DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
`create_user` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`update_date` datetime DEFAULT NULL,
`update_user` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`,`prefecture_id`,`industry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
update
正如我评论并根据在线手册所述的,我在下面标记了最终答案。
您可以将字符集设置为不同以支持日本字符集。字符集会影响服务器和客户端软件之间的存储和通信。
要使用Unicode,您可以做到这一点:
SET NAMES 'utf8';
可以为数据库级别指定字符集。 例如
CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
以及每张表。
CREATE TABLE tbl_name (column_list)
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]]
ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]
要保存其他语言,然后英语您的表格应该具有字符set utf-8。请确保您的表格为表格设置UTF-8。
刚找到答案,这里是;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;