1.字符集
mysql字符集分三种，一种是服务端字符集，一种是客户端字符集，还有一种是连接字符集。
1/服务端字符集
服务端字符集可以在启动时指定，如—character-set-server=utf8。默认字符集是latin1。如果创建数据库时没有特别指定字符集，那么将使用服务端的字符集。
创建数据库时指定字符集以及校验规则：
CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;
修改数据库的字符集以及校验规则：
ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;
查看数据库支持的字符集以及校验规则：
SHOW CHARACTER SET;
查看指定数据库的字符集以及校验规则：
USE db_name;
SELECT @@character_set_database, @@collation_database;
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';
以下三种情况将影响数据库的字符集以及校验规则：
1/CREATE DATABASE 时指定的字符集，如果没有指定则看—character-set-server变量。
2/LOAD DATA 语句，如果没有指定CHARACTER SET 语句，将会看—character-set-database变量。
3/存储过程的字符集将会使用创建时的字符集。
2/连接字符集与客户端字符集
连接字符集是用来转换从服务端传输到客户端的数据，当客户端发送一个语句时，会将客户端的字符集转换成连接字符集。—character-set-results表明了服务端将结果返回给客户端所使用的字符集。
SET NAMES { ‘charset_name’ [COLLATE ‘collation_name’ ] | DEFAULT }
set names 语句实际上是将—character-set-client，—character-set-connection，—character-set-results三个变量的值设置为同一个值。
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /app/mysql/share/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /app/mysql/share/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
SET CHARACTER SET charset_name;
这个语句等同于设置—character-set-clients，—character-set-results
2.数据类型
1/整型
tinyint [unsigned] [zerofill]
tinyint只占一个字节，8个bit位，带符号的是从-128~127 ，不带符号的是从0~255。
smallint [unsigned] [zerofill]
smallint只占两个字节，16个bit位，带符号的是从-32768~32767 ，不带符号的是从0~65536。
int [unsigned] [zerofill]
int 只占四个字节，32个bit位，带符号的是从-2147483648 ~2147483647，不带符号的是从0~4294967295。
bigint [unsigned] [zerofill]
bigint占8个字节，64个bit位，带符号的是从-9223372036854775808 ~9223372036854775807，不带符号的是从0~18446744073709551615。
zerofill的作用是用0来填充，通常配合括号中的位数来使用。如果指定了zerofill，那么mysql会自动添加unsigned属性。下面举个例子：
create table t1(id int(2) zerofill);
insert into t1 values(1),(127);
mysql> select * from t1;
+------+
| id |
+------+
| 01 |
| 127 |
+------+
2 rows in set (0.00 sec)
由上面的结果可以看到，int(2)中的这个2只是用来限制展示使用的，并不限制存储。括号中的数字代表了显示的位数，不足的用0来填充，如果位数超过了，则正常显示。
2/浮点型
decimal [M,[D]] [unsigned] [zerofill]
M表示一共的位数，D表示小数点之后的位数。M的最大值是65，D的最大值是30。如果没有D，则表示整数，如果没有指定M，则默认是10。
dec，numberic，fixed都是decimal的同义词。fixed同义词可以兼容其他数据库。
float [(M,D)] [unsigned] [zerofill]
double [(M,D)] [unsigned] [zerofill]
decimal是标准的浮点数类型，float，double是非标准的浮点数类型。
float占四个字节，double占8个字节，decimal(M,D)占M+2个字节，decimal是将数据以二进制的方式存储，所以不会损失精度。
3/时间类型
date 普通的时间类型，范围从’1000-01-01’~’9999-12-31’
datetime(fsp) 带时分秒的日期，范围从’1000-01-01 00:00:00.000000’~’9999-12-31 23:59:59.999999’
timestamp(fsp) 带时分秒的日期，范围从’1970-01-01 00:00:01.000000’~’2038-01-19 03:14:07.999999’
对于timestamp的处理方式由explicit_defaults_for_timestamp系统变量决定。默认情况下是disabled的，表中的第一个timestamp列，如果没有显示的分配一个值，那么会将当前日期的值插入进去。除了第一个timestamp列，其他都是’0000-00-00 00:00:00.000000’。sql_mode是一组语法检验法则，默认情况下，NO_ZERO_IN_DATE与NO_ZERO_DATE两个值表明了，不允许将零日期插入到mysql中。
当explicit_defaults_for_timestamp=enabled时，不会自动分配时间，允许null。
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
当sql_mode有NO_ZERO_IN_DATE,NO_ZERO_DATE时，且explicit_defaults_for_timestamp=disabled时，一个表中是不允许定义两个timestamp的。因为第二个timestamp会分配零日期。
mysql> create table t3(id int,time1 timestamp,time2 timestamp);
ERROR 1067 (42000): Invalid default value for 'time2'
下面把sql_mode清空，然后创建一个表有两个timestamp字段看看：
mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> create table t4(id int,time1 timestamp,time2 timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t4;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| time1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| time2 | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-----------+------+-----+---------------------+-----------------------------+
3 rows in set (0.00 sec)
当explicit_defaults_for_timestamp=on时，timestamp将不会有默认值。
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%explicit%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> create table t5(id int,date1 timestamp);
Query OK, 0 rows affected (0.00 sec)
mysql> desc t5;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| date1 | timestamp | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
当sql_mode=MAXDB时TIMESTAMP会被当做DATATIME来处理。(5.7.22，MAXDB已经被废弃)
time(fsp) ‘-838:59:59.000000’ ~ ‘838:59:59.000000’
year[(4)] 显示yyyy
关于datetime与timestamp的两个属性：default 与on update
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
插入时会自动插入当前日期，修改时也会修改该字段。
4/字符串类型
char char(0) 允许插入两个值null跟’’
varchar varcahr2(65535) 最大是65535，这个值表示字符，无论存放数字字母还是汉字，都可以放6个。这个65535是整个一行的数据的大小。如果建表时所有的字段的varchar超过了65535，将会报错。
mysql> create table t7(name varchar(6));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t7 values('人名');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t7 values('人名以');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t7 values('人名以后多少');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t7;
+--------------------+
| name |
+--------------------+
| 人名 |
| 人名以 |
| 人名以后多少 |
+--------------------+
3 rows in set (0.00 sec)
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
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存储varcahr的值时，使用1-2个字节存放数据的大小，如果存放的数据小于等于255个字节，那么将使用一个字节来存放，如果大于255个字节，那么将使用两个字节来存放。
char跟varchar的区别在于存储方式以及检索方式，当不足位数时，char会在右边填充空格。varchar则不会。检索时char会把填充的空格去掉。
字符集使用的是latin1，如果sql_mode没有那么严格，插入会成功，如果启用了严格的sql_mode,插入会失败。
| Value | CHAR(4) | Storage Required | VARCHAR(4) | Storage Required |
|---|---|---|---|---|
| ‘’ | ‘ ‘ | 4 bytes | ‘’ | 1 byte |
| ‘ab’ | ‘ab ‘ | 4 bytes | ‘ab’ | 3 bytes |
| ‘abcd’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 bytes |
| ‘abcdefgh’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 bytes |
mysql> CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO names VALUES ('Jones');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT myname = 'Jones', myname = 'Jones ' FROM names;
+------------------+--------------------+
| myname = 'Jones' | myname = 'Jones ' |
+------------------+--------------------+
| 1 | 1 |
+------------------+--------------------+
1 row in set (0.00 sec)
mysql> SELECT myname LIKE 'Jones', myname LIKE 'Jones ' FROM names;
+---------------------+-----------------------+
| myname LIKE 'Jones' | myname LIKE 'Jones ' |
+---------------------+-----------------------+
| 1 | 0 |
+---------------------+-----------------------+
1 row in set (0.00 sec)
TINYBLOB 最大255个字节，且使用一个字节存放数据的大小。
TINYTEXT 最大255个字符，且使用一个字节存放数据的大小。
BLOB(M) 最大65535个字节。使用两个字节存放数据的大小。
TEXT(M) 最大65535个字符，使用两个字节存放数据的大小。
LONGBLOB 最大4GB个字节。使用四个字节存放数据的大小。
LONGTEXT 最大4GB个字符，使用四个字节存放数据的大小。
text 通常用来存放不知道字段的最大长度。
Mysql中关于表中行的限制，一行的最大字节数为65535，所以创建表时的字节数不能超过65535，TEXT跟BLOB所占用的字节数为9-12。因为他们使用独立的页来存储。
对于Innodb的表，行最大的字节数略小于innodb_page_size的一半，默认情况下一个页的大小是16那么一行的最大是小于8KB，如果页的大小是64KB，那么最大是16KB。如果表中包含了变长字段(varchar)超过了行限制的大小，当填充完行限制之后，使用外部页来存放剩下的数据。
mysql> CREATE TABLE t4 (
c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using
ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768
bytes is stored inline.
BLOB时用来存储二进制大对象的，TEXT是用来存储文本型的大对象的。
ENUM(‘value1’,’value2’,’value3’….) 最多大概有3000个元素。一张表最多有255个enum或者set的组合。
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name | size |
+---------+--------+
| t-shirt | medium |
+---------+--------+
UPDATE shirts SET size = 'small' WHERE size = 'large';
COMMIT;
SET(‘value1’,’value2’,’value3’….)最多有64个不同的成员。一张表最多有255个enum或者set的组合。
若有收获，就点个赞吧
0 人点赞
