mysql 学习记录（七）--表优化、合成索引、浮点数与定点数

1.表优化
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> alter table t rename t_old;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t ( id varchar(100),content text);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values
    -> (1,repeat('haha',100)),
    -> (2,repeat('test',100)),
    -> (3,repeat('java',100));
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t select * from t;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table t rename t_optimize;
Query OK, 0 rows affected (0.01 sec)

mysql> select table_name,data_length from information_schema.tables where table_name = "t_optimize";
+------------+-------------+
| table_name | data_length |
+------------+-------------+
| t_optimize |       16384 |
+------------+-------------+
1 row in set (0.00 sec)

mysql> optimize table t_optimize;

+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| test1.t_optimize | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test1.t_optimize | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.47 sec)

mysql> select table_name,data_length from information_schema.tables where table_name = "t_optimize";
+------------+-------------+
| table_name | data_length |
+------------+-------------+
| t_optimize |       16384 |
+------------+-------------+
1 row in set (0.05 sec)

2.合成索引
mysql> create table t (id varchar(100),context blob,hash_value varchar(40));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values(1,repeat('zhejiang',2),md5(context));
Query OK, 1 row affected (0.02 sec)

mysql> insert into t values(1,repeat('jiangsu',2),md5(context));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(1,repeat('fujian 2008',2),md5(context));
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+------------------------+----------------------------------+
| id   | context                | hash_value                       |
+------+------------------------+----------------------------------+
| 1    | zhejiangzhejiang       | 8f36854efa45585f35e6ca54b7d58f31 |
| 1    | jiangsujiangsu         | e2817db97256ac13805ee1ccfc1048e7 |
| 1    | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |
+------+------------------------+----------------------------------+
3 rows in set (0.00 sec)

mysql> select * from t ;
+------+------------------------+----------------------------------+
| id   | context                | hash_value                       |
+------+------------------------+----------------------------------+
| 1    | zhejiangzhejiang       | 8f36854efa45585f35e6ca54b7d58f31 |
| 1    | jiangsujiangsu         | e2817db97256ac13805ee1ccfc1048e7 |
| 1    | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |
+------+------------------------+----------------------------------+
3 rows in set (0.00 sec)

mysql> select * from t
    ->  where hash_value=md5(repeat('fujian 2008',2));
+------+------------------------+----------------------------------+
| id   | context                | hash_value                       |
+------+------------------------+----------------------------------+
| 1    | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |
+------+------------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> desc t;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | varchar(100) | YES  |     | NULL    |       |
| context    | blob         | YES  |     | NULL    |       |
| hash_value | varchar(40)  | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>  create index inx_blob on t(context(100));
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+------------------------+----------------------------------+
| id   | context                | hash_value                       |
+------+------------------------+----------------------------------+
| 1    | zhejiangzhejiang       | 8f36854efa45585f35e6ca54b7d58f31 |
| 1    | jiangsujiangsu         | e2817db97256ac13805ee1ccfc1048e7 |
| 1    | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |
+------+------------------------+----------------------------------+
3 rows in set (0.00 sec)


mysql> desc select * from t where context like 'fujian%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: range
possible_keys: inx_blob
          key: inx_blob
      key_len: 103
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> desc select * from t where context like '%fujian' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table t rename t_index;
Query OK, 0 rows affected (0.02 sec)

3.浮点数、定点数
mysql> create table t(f float(8,1));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| f     | float(8,1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t values(1.23456);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+
| f    |
+------+
|  1.2 |
+------+
1 row in set (0.00 sec)

mysql> insert into t values(1.25456);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t;
+------+
| f    |
+------+
|  1.2 |
|  1.3 |
+------+
2 rows in set (0.00 sec)

mysql> create table test(c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values(131072.32,131072.32);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)