mysql学习（二）

mysql> drop table t2;
Query OK, 0 rows affected (0.05 sec)

mysql> create table t2(a int primary key ,b varchar(13000));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t2 select 1,lpad('a',12000,'a');
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 2,lpad('b',12000,'b');
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create index idx_t2 on t2(b);
Query OK, 0 rows affected, 2 warnings (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql> insert into t2 select 3,lpad('c',12000,'c');
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 4,lpad('d',12000,'d');
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 5,lpad('e',12000,'e');
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> explain select * from t2 where a='3';
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   |
 rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------+
|  1 | SIMPLE      | t2    | const | PRIMARY       | PRIMARY | 4       | const |
    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------+
1 row in set (0.00 sec)

mysql> delete from t2 where b<>1;
Query OK, 5 rows affected, 5 warnings (0.05 sec)

mysql> select count(1) from t2;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> insert into t2 select 5,lpad('e',12000,'e');
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> explain select * from t2 where a='3';
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL
 | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t2 where a='5';
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   |
 rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------+
|  1 | SIMPLE      | t2    | const | PRIMARY       | PRIMARY | 4       | const |
    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------+
1 row in set (0.00 sec)

mysql> explain select * from t2;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1
 |       |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
1 row in set (0.00 sec)

mysql> explain select a from t2;
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  |
rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 4       | NULL |
   1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
1 row in set (0.00 sec)

综上可以看到，表中有主键等索引时不一定使用。如果mysql发现根据主键值没找到对应的值，Extra列显示“ Impossible WHERE noticed after reading const tables”
根据查询的不同，type会显示是只查询索引（index），全表扫描方式查询（ALL），还是根据索引查询多列（const），或者没有匹配结果（NULL）

我在t2表上的b字段创建了普通索引，其建表语句为：

mysql> show create table t2;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------+
| Table | Create Table

             |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` varchar(13000) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_t2` (`b`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------+
1 row in set (0.00 sec)

mysql允许的最大列长度是65535，超过时报错。可以改为text或blobs等大字段方式存储。
mysql> alter table t2 add t3 varchar(13000);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table
type, not counting BLOBs, is 65535. You have to change some columns to TEXT or B
LOBs

接下来尝试新建一列，用于创建全文索引的实验
mysql> alter table t2 add column t3 varchar(1300);
Query OK, 1 row affected (0.27 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create fulltext index idx_t2b on t2(t3);
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

注意到上面的show create table语句中，t2表示innodb类型表，不能创建全文索引。只有myisam引擎的表才可以。

mysql> create table tab_a as select * from t2 engine=MYISAM;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '=MYIS
AM' at line 1

mysql> create table tab_a (a int(10),b varchar(12000)) engine=MYISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into tab_a select 5,lpad('e',12000,'e');
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create fulltext index idx_taba on tab_a(b);
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table tab_a;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----+
| Table | Create Table

     |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----+
| tab_a | CREATE TABLE `tab_a` (
  `a` int(10) DEFAULT NULL,
  `b` varchar(12000) DEFAULT NULL,
  FULLTEXT KEY `idx_taba` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----+
1 row in set (0.00 sec)

注意建表语句中对列长度不再限制。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-766650/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-766650/