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)
版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。
欢迎大家阅读《mysql 学习记要（七）-表优化、合成索引、浮点数与定点数_mysql》,跪求各位点评，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mysql 学习记要（七）-表优化、合成索引、浮点数与定点数_mysql
转载请注明原文链接：mysql 学习记要（七）-表优化、合成索引、浮点数与定点数_mysql
