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)

注意建表語句中對列長度不再限制。