mysql字符类型

一、查看表结构

1、desc t1;

2、explain t1;

3、show columns from t1;

二、查看表的定义

show create table t1;

三、char and varchar

varchar(n)  n的取值范围是0~65535    (在一个表中varchar 的长度总和不能超过65535)

当n的值小于255时，mysql额外需要1个字节去存储列的长度，当n大于255时，额外需要2个字节去存储列的长度

 

如：(ENGINE=InnoDB DEFAULT CHARSET=utf8)

mysql> create table t4(col varchar(10000),col2 varchar(10000),col3 varchar(1843));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> drop table t4;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t4(col varchar(10000),col2 varchar(10000),col3 varchar(1842));
Query OK, 0 rows affected (0.02 sec)

 

四、Binary and Varbinary  

按单字节方式存储（虽然查询显示为aa，但存储为aa\0\0,字符类型用空格填充，而二进制类型，用\0填充）

字符类型&二进制类型对应关系

BINARY-------------------->CHAR

VARBINARY-------------->VARCHAR

BLOB----------------------->TEXT

TINYBLOB----------------->TINYTEXT

MEDIUMBLOB------------>MEDIUMTEXT

LONGBLOB--------------->LONGTEXT

 

mysql> create table bin_t1(col1 binary(4));
Query OK, 0 rows affected (0.02 sec)

mysql> desc bin_t1;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| col1  | binary(4) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into bin_t1 set col1='aa';
Query OK, 1 row affected (0.01 sec)

mysql> select * from bin_t1;
+------+
| col1 |
+------+
| aa   |
+------+
1 row in set (0.01 sec)

mysql> select col1='aa\0\0' from bin_t1;
+---------------+
| col1='aa\0\0' |
+---------------+
|             1 |
+---------------+
1 row in set (0.02 sec)

 

mysql> select col1='aa\0' from bin_t1;
+-------------+
| col1='aa\0' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

 

 

五、ENUM  （a list of allowed values）  0-65536

  

mysql> create table enum_t1(
    -> col1 enum('F','M','UN'));
Query OK, 0 rows affected (0.02 sec)

mysql> desc enum_t1
    -> ;
+-------+--------------------+------+-----+---------+-------+
| Field | Type               | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| col1  | enum('F','M','UN') | YES  |     | NULL    |       |
+-------+--------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into enum_t1 values('a');
ERROR 1265 (01000): Data truncated for column 'col1' at row 1
mysql> select * from enum_t1;
Empty set (0.00 sec)

mysql> show variables like 'sql_mode' ;
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.01 sec)

mysql> insert into enum_t1 values('F');
Query OK, 1 row affected (0.00 sec)

mysql> select * from enum_t1;
+------+
| col1 |
+------+
| F    |
+------+
1 row in set (0.00 sec)

mysql> select col1+0 from enum_t1;
+--------+
| col1+0 |
+--------+
|      1 |
+--------+
1 row in set (0.01 sec)

mysql> insert into enum_t1 values('M');
Query OK, 1 row affected (0.00 sec)

mysql> select col1,col1+0 from enum_t1;
+------+--------+
| col1 | col1+0 |
+------+--------+
| F    |      1 |
| M    |      2 |
+------+--------+
2 rows in set (0.00 sec)

mysql> insert into enum_t1 values('UN');
Query OK, 1 row affected (0.03 sec)

mysql> select col1,col1+0 from enum_t1;
+------+--------+
| col1 | col1+0 |
+------+--------+
| F    |      1 |
| M    |      2 |
| UN   |      3 |
+------+--------+
3 rows in set (0.00 sec)

 

六、set  (最多64个不同的值)

 

mysql> create table set_t( col1 set('F','M','UN'));
Query OK, 0 rows affected (0.02 sec)

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

mysql> insert into set_t values('M');
Query OK, 1 row affected (0.00 sec)

mysql> insert into set_t values('F,M');
Query OK, 1 row affected (0.00 sec)

mysql> insert into set_t values('F,M,UN');
Query OK, 1 row affected (0.00 sec)

mysql> select col1,col1+0 from set_t;
+--------+--------+
| col1   | col1+0 |
+--------+--------+
| F      |      1 |
| M      |      2 |
| F,M    |      3 |
| F,M,UN |      7 |
+--------+--------+
4 rows in set (0.01 sec)

mysql>

 

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

mysql> select col1,col1+0 from set_t;
+--------+--------+
| col1   | col1+0 |
+--------+--------+
| F      |      1 |
| M      |      2 |
| F,M    |      3 |
| F,M,UN |      7 |
| UN     |      4 |
+--------+--------+
5 rows in set (0.00 sec)

从以上可以看出set values的索引位置（col1+0）计算方式为2的（n-1）次方，n为values在list中的位置