mysql 学习----表优化、合成索引、浮点数与定点数

本文主要是介绍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;
mysql> create table t ( id varchar(100),content text);
mysql> insert into t values
-> (1,repeat('haha',100)),
-> (2,repeat('test',100)),
-> (3,repeat('java',100));
mysql> insert into t select * from t;
mysql> alter table t rename t_optimize;
mysql> select table_name,data_length from information_schema.tables where table_name = "t_optimize";
+------------+-------------+
| table_name | data_length |
+------------+-------------+
| t_optimize | 16384 |
+------------+-------------+
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 |
+------------------+----------+----------+-------------------------------------------------------------------+
mysql> select table_name,data_length from information_schema.tables where table_name = "t_optimize";
+------------+-------------+
| table_name | data_length |
+------------+-------------+
| t_optimize | 16384 |
+------------+-------------+
2.合成索引
mysql> create table t (id varchar(100),context blob,hash_value varchar(40));
mysql> insert into t values(1,repeat('zhejiang',2),md5(context));
mysql> insert into t values(1,repeat('jiangsu',2),md5(context));
mysql> insert into t values(1,repeat('fujian 2008',2),md5(context));
mysql> select * from t;
+------+------------------------+----------------------------------+
| id | context | hash_value |
+------+------------------------+----------------------------------+
| 1 | zhejiangzhejiang | 8f36854efa45585f35e6ca54b7d58f31 |
| 1 | jiangsujiangsu | e2817db97256ac13805ee1ccfc1048e7 |
| 1 | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |
+------+------------------------+----------------------------------+
mysql> select * from t ;
+------+------------------------+----------------------------------+
| id | context | hash_value |
+------+------------------------+----------------------------------+
| 1 | zhejiangzhejiang | 8f36854efa45585f35e6ca54b7d58f31 |
| 1 | jiangsujiangsu | e2817db97256ac13805ee1ccfc1048e7 |
| 1 | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |
+------+------------------------+----------------------------------+
mysql> select * from t
-> where hash_value=md5(repeat('fujian 2008',2));
+------+------------------------+----------------------------------+
| id | context | hash_value |
+------+------------------------+----------------------------------+
| 1 | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |
+------+------------------------+----------------------------------+
mysql> desc t;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id | varchar(100) | YES | | NULL | |
| context | blob | YES | | NULL | |
| hash_value | varchar(40) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
mysql> create index inx_blob on t(context(100));
mysql> select * from t;
+------+------------------------+----------------------------------+
| id | context | hash_value |
+------+------------------------+----------------------------------+
| 1 | zhejiangzhejiang | 8f36854efa45585f35e6ca54b7d58f31 |
| 1 | jiangsujiangsu | e2817db97256ac13805ee1ccfc1048e7 |
| 1 | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |
+------+------------------------+----------------------------------+
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
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
3.浮点数、定点数
mysql> create table t(f float(8,1));
mysql> desc t;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| f | float(8,1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
mysql> insert into t values(1.23456);
mysql> select * from t;
+------+
| f |
+------+
| 1.2 |
+------+
mysql> insert into t values(1.25456);
mysql> select * from t;
+------+
| f |
+------+
| 1.2 |
| 1.3 |
+------+
mysql> create table test(c1 float(10,2),c2 decimal(10,2));
mysql> insert into test values(131072.32,131072.32);
mysql> select * from test;
+-----------+-----------+
| c1 | c2 |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+