CREATE TABLE `user` ( `user_name` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, PRIMARY KEY (`user_name`),) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `admin_user` ( `user_name` varchar(50) NOT NULL, `admin_type` char(1) NOT NULL default '1', UNIQUE KEY `user_name` (`user_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; user_name一个varchar(255),一个50,符合手册上说的列定义不匹配, explain select * from admin_user as a , user as u where a.user_name=u.user_name; 结果 id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+ | 1 | SIMPLE | a | ALL | user_name | NULL | NULL | NULL | 19 | | | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 767 | cc9.a.user_name | 1 | Using where 我看手册上举的例子也是一个表的主键和另一个表带索引的列关联,不知道为什么我这里是eq_ref
用你的表做了个试验,的确是你的说的这种情况, 然后又查了一下手册。 One problem here is that MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. tt.ActualPC is declared as CHAR(10) and et.EMPLOYID is CHAR(15), so there is a length mismatch. 手册上的情况和你的不太一样,手册上所指的是MYSQL如果在不同的索引中进行优化选择。而我们现这测试的这个例子中其实MYSQL已经别无选择,只有这么个索引可用。
`user_name` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`user_name`),) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `admin_user` (
`user_name` varchar(50) NOT NULL,
`admin_type` char(1) NOT NULL default '1',
UNIQUE KEY `user_name` (`user_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
user_name一个varchar(255),一个50,符合手册上说的列定义不匹配,
explain select * from admin_user as a , user as u where a.user_name=u.user_name;
结果
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+
| 1 | SIMPLE | a | ALL | user_name | NULL | NULL | NULL | 19 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 767 | cc9.a.user_name | 1 | Using where 我看手册上举的例子也是一个表的主键和另一个表带索引的列关联,不知道为什么我这里是eq_ref
然后又查了一下手册。
One problem here is that MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. tt.ActualPC is declared as CHAR(10) and et.EMPLOYID is CHAR(15), so there is a length mismatch. 手册上的情况和你的不太一样,手册上所指的是MYSQL如果在不同的索引中进行优化选择。而我们现这测试的这个例子中其实MYSQL已经别无选择,只有这么个索引可用。