选择合适的数据类型

mysql> drop table vc;
 Query OK, 0 rows affected (0.03 sec)

mysql> create table vc(v varchar(4),c char(4));
 Query OK, 0 rows affected (0.04 sec)

mysql> insert into vc values('ab  ','ab  ');
 Query OK, 1 row affected (0.01 sec)

mysql> select concat(v,'+'),concat(c,'+') from vc;
 +-------------+---------------+
 | concat('+') | concat(c,'+') |
 +-------------+---------------+
 | +           | ab+           |
 +-------------+---------------+
 1 row in set (0.00 sec)

mysql> insert into vc values('ab ','ab  ');
 Query OK, 1 row affected (0.02 sec)

mysql> select concat(v,'+'),concat(c,'+') from vc;
 +---------------+---------------+
 | concat(v,'+') | concat(c,'+') |
 +---------------+---------------+
 | ab  +         | ab+           |
 | ab +          | ab+           |
 +---------------+---------------+
 2 rows in set (0.00 sec)

 mysql> drop table t;
 Query OK, 0 rows affected (0.02 sec)

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

mysql> insert into t values(1,repeat('haha',100));
 Query OK, 1 row affected (0.01 sec)

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

mysql> insert into t values(3,repeat('haha',100));
 Query OK, 1 row affected (0.01 sec)

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

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

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

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

[root@dba test]# du -sh t.*
 12K t.frm
 188M t.ibd

mysql> delete from t where id=1;
 Query OK, 131072 rows affected (4.90 sec)

mysql> commit;
 Query OK, 0 rows affected (0.00 sec)

[root@dba test]# du -sh t.*
 12K t.frm
 188M t.ibd

mysql> optimize table t;
 +--------+----------+----------+-------------------------------------------------------------------+
 | Table  | Op       | Msg_type | Msg_text                                                          |
 +--------+----------+----------+-------------------------------------------------------------------+
 | test.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
 | test.t | optimize | status   | OK                                                                |
 +--------+----------+----------+-------------------------------------------------------------------+
 2 rows in set (22.60 sec)

[root@dba test]# du -sh t.*
 12K t.frm
 120M t.ibd

mysql> create table t(id varchar(100),context blob,hash_value varchar(40));
Query OK, 0 rows affected (0.09 sec)

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

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

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

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+--------------------------+----------------------------------+
| id   | context                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 1    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 3    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
3 rows in set (0.00 sec)

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

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

ERROR:
No query specified

mysql> create table t1(f float(8,1));
Query OK, 0 rows affected (0.04 sec)

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

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

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

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

mysql> create table test(c1 float(10,2),decimal(10,2));
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 'decimal(10,2))' at line 1
mysql> create table test(c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.04 sec)

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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29313086/viewspace-1735335/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29313086/viewspace-1735335/