mysql中的varchar类型_MYSQL中的varchar类型

我们在应用中常常使用的varchar类型，在mysql中的varchar类型占用的空间比在ORACLE中的varchar2类型占用的空间要多很多,下面举一个示例：

mysql> select count(*) from t1;

+----------+

| count(*) |

+----------+

|  8388608 |

+----------+

1 row in set (8.89 sec)

mysql> show create table t1;

+-------+-------------------------------------

| Table | Create Table

+-------+-------------------------------------

| t1    | CREATE TABLE `t1` (

`nick` varchar(32) DEFAULT NULL,

`gmt_create` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-------------------------------------

1 row in set (0.00 sec)

--MYSQL innodb占用空间324M

[root@testapp74 test]# ls -lh

total 325M

-rw-rw----  1 mysql mysql   61 Jul 18 13:32 db.opt

-rw-rw----  1 mysql mysql 8.5K Jul 18 13:46 t1.frm

-rw-rw----  1 mysql mysql 324M Jul 18 13:48 t1.ibd

--ORACLE中插入的数据与mysql相同，占用的空间只有142M

create table T1

(

NICK       VARCHAR2(32),

GMT_CREATE DATE

);

SQL> select count(*) from t1;

COUNT(*)

----------

8388608

SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T1';

SEGMENT_NAME                   BYTES/1024/1024

------------------------------ ---------------

T1                                         142

--上面可能会有时间类型的干扰,我们把时间字段删除

mysql> alter table t1 drop column gmt_create;

Query OK, 8388609 rows affected (1 min 38.40 sec)

Records: 8388609  Duplicates: 0  Warnings: 0

[root@testapp74 test]# ls -lh

total 253M

-rw-rw----  1 mysql mysql   61 Jul 18 13:32 db.opt

-rw-rw----  1 mysql mysql 8.4K Jul 18 15:34 t1.frm

-rw-rw----  1 mysql mysql 252M Jul 18 15:36 t1.ibd

--oracle,为了保证公平性(mysql每次ddl操作都会重构表,有一个copy的动作)，在drop完列后，并对表进行move

SQL> alter table t1 drop column gmt_create;

Table altered.

SQL> col segment_name for a30

SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T1';

SEGMENT_NAME                   BYTES/1024/1024

------------------------------ ---------------

T1                                         142

SQL> alter table t1 move;

Table altered.

SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T1';

SEGMENT_NAME                   BYTES/1024/1024

------------------------------ ---------------

T1                                         101

--在此种实验情况下，我们可以看出,mysql所占用的空间是ORACLE所占用空间的两倍

--看来varchar类型的字段,在列字段实际插入的字符长度之外，mysql在处理时还会多申请一些空间，可能这个主要是为了以后的更新作准备.

--这里还有另外一种现象，当插入的列值超过长度时,mysql并不返回应用端错误,而是"help"你truncate超过长度的字符串,然后再插入进去

--这个特性,如果在应用端没有做字符串长度检查，则可能会把我们的数据被截取了,而我们还不知道.

mysql> show create table t1;

+-------+-----------------------------------

| Table | Create Table

+-------+-----------------------------------

| t1    | CREATE TABLE `t1` (

`nick` varchar(32) DEFAULT NULL,

`gmt_create` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-----------------------------------

1 row in set (0.00 sec)

mysql> insert into t1 values(rpad('a',50,'b'),now());

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select rpad('a',50,'b');

+----------------------------------------------------+

| rpad('a',50,'b')                                   |

+----------------------------------------------------+

| abbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb |

+----------------------------------------------------+

1 row in set (0.00 sec)

mysql> select length(rpad('a',50,'b'));

+--------------------------+

| length(rpad('a',50,'b')) |

+--------------------------+

|                       50 |

+--------------------------+

1 row in set (0.00 sec)

mysql> select * from t1 where nick like 'abbbbbbbbbb%';

+----------------------------------+---------------------+

| nick                             | gmt_create          |

+----------------------------------+---------------------+

| abbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb | 2008-07-18 15:21:06 |

+----------------------------------+---------------------+

1 row in set (11.23 sec)

mysql> select length(nick),nick,gmt_create from t1 where nick like 'abbbbbbbbbb%';

+--------------+----------------------------------+---------------------+

| length(nick) | nick                             | gmt_create          |

+--------------+----------------------------------+---------------------+

|           32 | abbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb | 2008-07-18 15:21:06 |

+--------------+----------------------------------+---------------------+

1 row in set (11.21 sec)

知道的更多，我们才知道怎么更好的使用mysql.