1.表优化mysql> use test1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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: 0mysql> insert into t select * from t;Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> 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: 0mysql> 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: rangepossible_keys: inx_blob key: inx_blob key_len: 103 ref: NULL rows: 1 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> desc select * from t where context like '%fujian' \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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)版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。