今天晚上在家里面的电脑上，又对userinfo.onLineTimeArray列进行同样方式的测试，通过数据库类型定义效率探索（一）与这篇博客，得出结论。
1、在表接近5万的数据量下，一个字段设置为char、varchar、text后，该表占用的空间并没有太大的差别，根据该字段查询的速度，也没有明显的差别。
2、varchar(50)与varchar(1500)，占用的表空间是一样的。表占用空间的大小主要还是由表里面的数据决定。
3、表空间的大小还与列的存在有微弱的关系。如有onLineTimeArray字段比没有onLineTimeArray字段要大5.784-5.741=0.043M。
下面是测试记录：
总数量：46416
字段类型：
| onLineTimeArray | varchar(1500) | YES | | NULL | |
userinfo表占用空间data_size
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 9.323MB | 268435455.999MB | 0.000MB | 0.540MB |
+-----------+-----------------+-----------+--------------+
模糊查询
mysql> select count(*) from userinfo where onLineTimeArray like '%1%';
+----------+
| count(*) |
+----------+
| 33064 |
+----------+
1 row in set (0.09 sec)
然后修改onLineTimeArray：varchar(1500) default null->text(1500) not null，并模糊查询，然后查看表占用空间，如下：
mysql> alter table userinfo modify onLineTimeArray text(1500) not null default '';
ERROR 1101 (42000): BLOB/TEXT column 'onLineTimeArray' can't have a default value
mysql> alter table userinfo modify onLineTimeArray text not null;
Query OK, 46416 rows affected (0.73 sec)
Records: 46416 Duplicates: 0 Warnings: 0
mysql> select count(*) from userinfo where onLineTimeArray like '%1%';
+----------+
| count(*) |
+----------+
| 33064 |
+----------+
1 row in set (0.09 sec)
mysql> select concat(truncate(sum(data_length)/1024/1024,3),'MB') as data_size,
-> concat(truncate(sum(max_data_length)/1024/1024,3),'MB') as max_data_length,
-> concat(truncate(sum(data_free)/1024/1024,3),'MB') as data_free,
-> concat(truncate(sum(index_length)/1024/1024,3),'MB') as index_length
-> from information_schema.tables where table_name='userinfo';
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 9.336MB | 268435455.999MB | 0.000MB | 0.540MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.01 sec)
可以看到查询，用了0.09ms，比varchar(1500)要慢0.01ms；表空间增加了9.336MB-9.323MB=0.013M。
结论：在46416数据量下，对于表中字段类型，char、varchar、text并不能明显的影响表占用空间的大小与查询的速度。
--------------------------------------------------------------------------------------------------------------
将userinfo还原回来，然后执行下面的sql语句
mysql> desc userinfo;
+-----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+----------------+
| _id | int(11) | NO | PRI | NULL | auto_increment |
| imei | varchar(25) | NO | | | |
| releas | varchar(25) | YES | | NULL | |
| phoneNum | varchar(16) | YES | | NULL | |
| app | varchar(30) | NO | | | |
| versionCode | int(11) | YES | | 0 | |
| versionName | varchar(6) | YES | | NULL | |
| location | varchar(1100) | YES | | NULL | |
| onLineTimeArray | varchar(1500) | YES | | NULL | |
| integral | int(11) | YES | | 10 | |
| date | varchar(19) | YES | | NULL | |
+-----------------+---------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)
mysql> select count(*) from userinfo;
+----------+
| count(*) |
+----------+
| 46416 |
+----------+
1 row in set (0.00 sec)
mysql> select concat(truncate(sum(data_length)/1024/1024,3),'MB') as data_size,
-> concat(truncate(sum(max_data_length)/1024/1024,3),'MB') as max_data_lengt
h,
-> concat(truncate(sum(data_free)/1024/1024,3),'MB') as data_free,
-> concat(truncate(sum(index_length)/1024/1024,3),'MB') as index_length
-> from information_schema.tables where table_name='userinfo';
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 9.323MB | 268435455.999MB | 0.000MB | 0.540MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.01 sec)
清空onLineTimeArray的数据，并用引号（''）替换
mysql> update userinfo set onLineTimeArray='';
Query OK, 33192 rows affected (1.76 sec)
Rows matched: 46416 Changed: 33192 Warnings: 0
mysql> select concat(truncate(sum(data_length)/1024/1024,3),'MB') as data_size,
-> concat(truncate(sum(max_data_length)/1024/1024,3),'MB') as max_data_length,
-> concat(truncate(sum(data_free)/1024/1024,3),'MB') as data_free,
-> concat(truncate(sum(index_length)/1024/1024,3),'MB') as index_length
-> from information_schema.tables where table_name='userinfo';
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 9.323MB | 268435455.999MB | 2.933MB | 0.540MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.01 sec)
mysql> optimize table userinfo;
+---------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+----------+----------+----------+
| swanflysql.userinfo | optimize | status | OK |
+---------------------+----------+----------+----------+
1 row in set (0.34 sec)
mysql> select concat(truncate(sum(data_length)/1024/1024,3),'MB') as data_size,
-> concat(truncate(sum(max_data_length)/1024/1024,3),'MB') as max_data_lengt
h,
-> concat(truncate(sum(data_free)/1024/1024,3),'MB') as data_free,
-> concat(truncate(sum(index_length)/1024/1024,3),'MB') as index_length
-> from information_schema.tables where table_name='userinfo';
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 5.784MB | 268435455.999MB | 0.000MB | 0.454MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.01 sec)
此时onLineTimeArray字段：
| onLineTimeArray | varchar(1500) | YES | | NULL | |
将onLineTimeArray 改为：varchar(1500) default null->varchar(50) default null
mysql> alter table userinfo modify onLineTimeArray varchar(50) default null;
Query OK, 46416 rows affected (0.53 sec)
Records: 46416 Duplicates: 0 Warnings: 0
mysql> select concat(truncate(sum(data_length)/1024/1024,3),'MB') as data_size,
-> concat(truncate(sum(max_data_length)/1024/1024,3),'MB') as max_data_lengt
h,
-> concat(truncate(sum(data_free)/1024/1024,3),'MB') as data_free,
-> concat(truncate(sum(index_length)/1024/1024,3),'MB') as index_length
-> from information_schema.tables where table_name='userinfo';
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 5.784MB | 268435455.999MB | 0.000MB | 0.540MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.01 sec)
可以看到data_size并没有改变，index_length增加了0.540-0.454MB=0.086M
接着测试，将onLineTimeArray：varchar(50) default null->char(50) default null;
结果没有变。
再接着测试，将onLineTimeArray这一列删掉
mysql> alter table userinfo drop onLineTimeArray;
Query OK, 46416 rows affected (0.62 sec)
Records: 46416 Duplicates: 0 Warnings: 0
mysql> select concat(truncate(sum(data_length)/1024/1024,3),'MB') as data_size,
-> concat(truncate(sum(max_data_length)/1024/1024,3),'MB') as max_data_lengt
h,
-> concat(truncate(sum(data_free)/1024/1024,3),'MB') as data_free,
-> concat(truncate(sum(index_length)/1024/1024,3),'MB') as index_length
-> from information_schema.tables where table_name='userinfo';
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 5.741MB | 268435455.999MB | 0.000MB | 0.540MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.01 sec)
data_size减少了5.784-5.741=0.043M。
