搞不明白为什么会报错这样就可以
insert into web (loginname,showname,groupname,visit,webtype,url)
values ('chenye','陈远二','/南方销售公司/','访问网站','新闻门户','ctc.qzs.qq.com/qzone/newblog/blogcanvas.html?uin=453913456&keyname=2&params=&qz_style=1')
这样就报错 1366 incorrect string value
这个语句我用了limit 1测试,结果集就是上面的插入值
insert into web (loginname,showname,groupname,visit,webtype,url)
select * from
(
select b.name as bname, b.show_name,c.group_name,d.name as dname,e.name as ename,
left(substring(substring(f.result,locate('<_f n="url">',f.result)),locate('>',substring(f.result,locate('<_f n="url">',f.result)))+1),
locate('<',substring(substring(f.result,locate('<_f n="url">',f.result)),locate('>',substring(f.result,locate('<_f n="url">',f.result)))+1))-1
) as '网址'
from
(
select * from (select A.auto_id, A.record_id, A.account_id, A.dev_id, A.user_crc, A.group_crc, host_ip, dst_ip, private_type, serv_crc, app_crc, record_time, net_action, index3, index4
from A20110315 A use index(index_auto_id), AcGroupInfo
where private_type in (1, 16) and group_crc_2 =
(select group_crc from groupname where group_name like '%南方销售公司%' limit 1)
and user_crc =
(select crc from username where show_name like '%陈远二%' limit 1)
and group_crc_1 = 2043925204 and AcGroupInfo.group_crc = A.group_crc and serv_crc = 3281598801 and (A.index3 = 0 or A.index3 = 1) and (A.net_action = 2 or A.net_action = 3)
order by auto_id
) _TMP_TABLE_ order by _TMP_TABLE_.auto_id desc
) a
join UserName b on a.user_crc = b.crc
join GroupName c on a.group_crc = c.group_crc
join crc_name d on a.serv_crc = d.crc
join crc_name e on a.app_crc = e.crc
join U20110315 f on a.record_id = f.record_id and a.account_id= f.account_id and a.dev_id=f.dev_id
limit 1
) as tb
insert into web (loginname,showname,groupname,visit,webtype,url)
values ('chenye','陈远二','/南方销售公司/','访问网站','新闻门户','ctc.qzs.qq.com/qzone/newblog/blogcanvas.html?uin=453913456&keyname=2&params=&qz_style=1')
这样就报错 1366 incorrect string value
这个语句我用了limit 1测试,结果集就是上面的插入值
insert into web (loginname,showname,groupname,visit,webtype,url)
select * from
(
select b.name as bname, b.show_name,c.group_name,d.name as dname,e.name as ename,
left(substring(substring(f.result,locate('<_f n="url">',f.result)),locate('>',substring(f.result,locate('<_f n="url">',f.result)))+1),
locate('<',substring(substring(f.result,locate('<_f n="url">',f.result)),locate('>',substring(f.result,locate('<_f n="url">',f.result)))+1))-1
) as '网址'
from
(
select * from (select A.auto_id, A.record_id, A.account_id, A.dev_id, A.user_crc, A.group_crc, host_ip, dst_ip, private_type, serv_crc, app_crc, record_time, net_action, index3, index4
from A20110315 A use index(index_auto_id), AcGroupInfo
where private_type in (1, 16) and group_crc_2 =
(select group_crc from groupname where group_name like '%南方销售公司%' limit 1)
and user_crc =
(select crc from username where show_name like '%陈远二%' limit 1)
and group_crc_1 = 2043925204 and AcGroupInfo.group_crc = A.group_crc and serv_crc = 3281598801 and (A.index3 = 0 or A.index3 = 1) and (A.net_action = 2 or A.net_action = 3)
order by auto_id
) _TMP_TABLE_ order by _TMP_TABLE_.auto_id desc
) a
join UserName b on a.user_crc = b.crc
join GroupName c on a.group_crc = c.group_crc
join crc_name d on a.serv_crc = d.crc
join crc_name e on a.app_crc = e.crc
join U20110315 f on a.record_id = f.record_id and a.account_id= f.account_id and a.dev_id=f.dev_id
limit 1
) as tb
1366 - Incorrect string value:"\x99\x88\E8\xBF\x9C\xE4..." for column 'showName' at row 1
mysql> show create table web;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| web | CREATE TABLE `web` (
`loginName` varchar(255) default NULL,
`showName` varchar(255) default NULL,
`groupName` varchar(255) default NULL,
`visit` varchar(255) default NULL,
`webtype` varchar(255) default NULL,
`url` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
MySQL 中文显示乱码