我的查询很简单:EXPLAIN SELECT * FROM de_company WHERE username='hzsanli';(username='hzsanli'这条记录在数据表里是存在的;)
显示如下:
id select_type TABLE TYPE possible_keys key_len ref ROWS extra
1 SIMPLE de_company const username 62 const SELECT LENGTH(`username`),`username` FROM de_company ORDER BY LENGTH(`username`) ASC LIMIT 200;显示有username为空的记录;数据结构如下:create table
CREATE TABLE `de_company` (
`company_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` bigint(20) unsigned NOT NULL DEFAULT '0',
`username` varchar(30) NOT NULL DEFAULT '',
`groupid` smallint(4) unsigned NOT NULL DEFAULT '0',
`company` varchar(100) NOT NULL DEFAULT '',
`level` tinyint(1) unsigned NOT NULL DEFAULT '0',
`validated` tinyint(1) unsigned NOT NULL DEFAULT '0',
`validator` varchar(100) NOT NULL DEFAULT '',
`validtime` int(10) unsigned NOT NULL DEFAULT '0',
`vip` smallint(2) unsigned NOT NULL DEFAULT '0',
`vipt` smallint(2) unsigned NOT NULL DEFAULT '0',
`vipr` smallint(2) NOT NULL DEFAULT '0',
`type` varchar(100) NOT NULL DEFAULT '贸易商',
`catid` varchar(100) NOT NULL DEFAULT '',
`catids` varchar(100) NOT NULL DEFAULT '',
`areaid` int(10) unsigned NOT NULL DEFAULT '2',
`mode` varchar(100) NOT NULL DEFAULT '',
`capital` float unsigned NOT NULL DEFAULT '5',
`regunit` varchar(15) NOT NULL DEFAULT '人民币',
`size` varchar(100) NOT NULL DEFAULT '',
`regyear` varchar(4) NOT NULL DEFAULT '',
`regcity` varchar(30) NOT NULL DEFAULT '',
`sell` varchar(255) NOT NULL DEFAULT '',
`buy` varchar(255) NOT NULL DEFAULT '',
`business` varchar(255) NOT NULL DEFAULT '',
`telephone` varchar(50) NOT NULL DEFAULT '',
`fax` varchar(50) NOT NULL DEFAULT '',
`mail` varchar(50) NOT NULL DEFAULT '',
`address` varchar(255) NOT NULL DEFAULT '',
`postcode` varchar(20) NOT NULL DEFAULT '',
`homepage` varchar(255) NOT NULL DEFAULT '',
`fromtime` int(10) unsigned NOT NULL DEFAULT '0',
`totime` int(10) unsigned NOT NULL DEFAULT '0',
`styletime` int(10) unsigned NOT NULL DEFAULT '0',
`thumb` varchar(255) NOT NULL DEFAULT '',
`introduce` varchar(255) NOT NULL DEFAULT '暂无简介',
`hits` int(10) unsigned NOT NULL DEFAULT '0',
`keyword` varchar(255) NOT NULL DEFAULT '',
`banner` varchar(255) NOT NULL DEFAULT '',
`template` varchar(30) NOT NULL DEFAULT '',
`skin` varchar(30) NOT NULL DEFAULT '',
`domain` varchar(100) NOT NULL DEFAULT '',
`icp` varchar(100) NOT NULL DEFAULT '',
`linkurl` varchar(255) NOT NULL DEFAULT '',
`user_id` char(36) NOT NULL,
`simple` varchar(200) NOT NULL DEFAULT '暂无简写',
PRIMARY KEY (`company_id`),
UNIQUE KEY `userid` (`userid`),
UNIQUE KEY `username` (`username`),
KEY `company` (`company`)
) ENGINE=MyISAM AUTO_INCREMENT=269165 DEFAULT CHARSET=gbk COMMENT='公司'真不知道数据表错在哪里了
PS:其他字段查询是可以显示的;如果执行update de_company set username='test' where username='hzsanli'这样执行是成功的。
显示如下:
id select_type TABLE TYPE possible_keys key_len ref ROWS extra
1 SIMPLE de_company const username 62 const SELECT LENGTH(`username`),`username` FROM de_company ORDER BY LENGTH(`username`) ASC LIMIT 200;显示有username为空的记录;数据结构如下:create table
CREATE TABLE `de_company` (
`company_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` bigint(20) unsigned NOT NULL DEFAULT '0',
`username` varchar(30) NOT NULL DEFAULT '',
`groupid` smallint(4) unsigned NOT NULL DEFAULT '0',
`company` varchar(100) NOT NULL DEFAULT '',
`level` tinyint(1) unsigned NOT NULL DEFAULT '0',
`validated` tinyint(1) unsigned NOT NULL DEFAULT '0',
`validator` varchar(100) NOT NULL DEFAULT '',
`validtime` int(10) unsigned NOT NULL DEFAULT '0',
`vip` smallint(2) unsigned NOT NULL DEFAULT '0',
`vipt` smallint(2) unsigned NOT NULL DEFAULT '0',
`vipr` smallint(2) NOT NULL DEFAULT '0',
`type` varchar(100) NOT NULL DEFAULT '贸易商',
`catid` varchar(100) NOT NULL DEFAULT '',
`catids` varchar(100) NOT NULL DEFAULT '',
`areaid` int(10) unsigned NOT NULL DEFAULT '2',
`mode` varchar(100) NOT NULL DEFAULT '',
`capital` float unsigned NOT NULL DEFAULT '5',
`regunit` varchar(15) NOT NULL DEFAULT '人民币',
`size` varchar(100) NOT NULL DEFAULT '',
`regyear` varchar(4) NOT NULL DEFAULT '',
`regcity` varchar(30) NOT NULL DEFAULT '',
`sell` varchar(255) NOT NULL DEFAULT '',
`buy` varchar(255) NOT NULL DEFAULT '',
`business` varchar(255) NOT NULL DEFAULT '',
`telephone` varchar(50) NOT NULL DEFAULT '',
`fax` varchar(50) NOT NULL DEFAULT '',
`mail` varchar(50) NOT NULL DEFAULT '',
`address` varchar(255) NOT NULL DEFAULT '',
`postcode` varchar(20) NOT NULL DEFAULT '',
`homepage` varchar(255) NOT NULL DEFAULT '',
`fromtime` int(10) unsigned NOT NULL DEFAULT '0',
`totime` int(10) unsigned NOT NULL DEFAULT '0',
`styletime` int(10) unsigned NOT NULL DEFAULT '0',
`thumb` varchar(255) NOT NULL DEFAULT '',
`introduce` varchar(255) NOT NULL DEFAULT '暂无简介',
`hits` int(10) unsigned NOT NULL DEFAULT '0',
`keyword` varchar(255) NOT NULL DEFAULT '',
`banner` varchar(255) NOT NULL DEFAULT '',
`template` varchar(30) NOT NULL DEFAULT '',
`skin` varchar(30) NOT NULL DEFAULT '',
`domain` varchar(100) NOT NULL DEFAULT '',
`icp` varchar(100) NOT NULL DEFAULT '',
`linkurl` varchar(255) NOT NULL DEFAULT '',
`user_id` char(36) NOT NULL,
`simple` varchar(200) NOT NULL DEFAULT '暂无简写',
PRIMARY KEY (`company_id`),
UNIQUE KEY `userid` (`userid`),
UNIQUE KEY `username` (`username`),
KEY `company` (`company`)
) ENGINE=MyISAM AUTO_INCREMENT=269165 DEFAULT CHARSET=gbk COMMENT='公司'真不知道数据表错在哪里了
PS:其他字段查询是可以显示的;如果执行update de_company set username='test' where username='hzsanli'这样执行是成功的。
可是sqlyog查询其他的表,同样数量级的都没有挂掉,这岂不是太奇怪了?
这个aaa是表中另一个存在的名字,来查一下,会如何?再分析一下aaa 和 'hzsanli' 有什么区别,可能会有些线索。
你就加limit xx,xx,一行行select出来吧, 看到底哪行有问题
除了username这个字段外,还有一个字段也存在问题userid,这个字段是存用户的账号序号的,bigint(20) unsigned类型的,这个键我建立了唯一性索引的,username字段也建立了唯一性索引的
"userid","bigint(20) unsigned",\N,"NO","UNI","0","","select,insert,update,references",""
"username","varchar(30)","gbk_chinese_ci","NO","UNI","","","select,insert,update,references",""