mysql字符集问题_mysql字符集问题

########常用命令

mysql> show collation;

mysql> show character set;

mysql> show character set like '%utf8%';

mysql> show table status;

#########参数文件中定义

character_set_server=utf8mb4

默认

##########命令举例

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show character set like '%utf8%';

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

| Charset | Description | Default collation | Maxlen |

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

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |

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

2 rows in set (0.00 sec)

mysql>

###行注释

Charset ： 字符集

Description 描述

Default collation ： 默认排序、插入检查规则 ci

Maxlen ： 最大长度

####################

●配置参数character_set_server

●CREATE DATABASE

●CREATE TABLE

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_ specification] ...

create_ specification:

[DEFAULT] CHARACTER SET [=] charset_ name

CREATE TABLE t( a CHAR(10))CHARSET=utf8mb4;

| [DEFAULT] COLLATE [=] collation_ _name

CREATE TABLEt (

a VARCHAR(10) CHARSET gbk,

b VARCHAR (10) CHARSET latin1,

C VARCHAR (10) ) CHARSET=utf8mb4;

#########数据有效性约束：

ENUM & SET

●字符串类型--集合类型

●ENUM类型最多允许65536个值

●SET类型最多允许64个值

●通过sql_mode参数可以用于约束检查

mysql> CREATE TABLE t(

> user VARCHAR(30),

> sex ENUM('male',female')

->)ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

mysql> SET sQL _MODE='strict _trans. _tables';

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SELECT "David",'male';

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> INSERT INTO t SELECT 'John','bimale';

ERROR 1265 (01000); Data truncated for column 'sex' at row 1

###############关于ENUM举例：#####################################

mysql> set sql_mode=''; #先不设置sql_mode

Query OK, 0 rows affected (0.00 sec)

mysql> create table a ( a enum ('m','f')); #创建一张表，字段类型为enum

Query OK, 0 rows affected (0.01 sec)

mysql> desc a; #查看这表

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

| Field | Type | Null | Key | Default | Extra |

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

| a | enum('m','f') | YES | | NULL | |

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

1 row in set (0.00 sec)

mysql> insert into a select 'm'; #插入一个合法值

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from a; #查看

+------+

| a |

+------+

| m |

+------+

1 row in set (0.00 sec)

mysql> insert into a select 'y';#插入一个非法值

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

Records: 1 Duplicates: 0 Warnings: 1

mysql> select * from a;#查看，虽然没有报错，但是插入的是空值

+------+

| a |

+------+

| m |

| |

+------+

2 rows in set (0.00 sec)

mysql> set sql_mode='STRICT_TRANS_TABLES'; #修改sql_mode

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into a select 'y';#再插入一个非法值

ERROR 1265 (01000): Data truncated for column 'a' at row 1

mysql> select * from a;#查看，插入失败

+------+

| a |

+------+

| m |

| |

+------+

2 rows in set (0.00 sec)

mysql>

############时间#########################

占用字节表示范围

DATETIME81000-01-01 00:00:00 ~ 9999-12-31 23:59:59

DATE，31000-01-01 ~ 9999-12-31

TIMESTAMP41970-01-01 00:00:00 UTC ~2038-01-19 03:14:07 UTC

YEAR1YEAR(2): 1970 ~ 2070

YEAR(4): 1901 ~ 2155

TIME3-838:59:59 ~838:59:59

#TIMESTAMP有时区

####时区举例###################

mysql> drop table a;

Query OK, 0 rows affected (0.00 sec)

mysql> create table a (a TIMESTAMP,b datetime);

Query OK, 0 rows affected (0.00 sec)

mysql> desc a ;

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

| Field | Type | Null | Key | Default | Extra |

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

| a | timestamp | YES | | NULL | |

| b | datetime | YES | | NULL | |

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

2 rows in set (0.00 sec)

mysql> insert into a select now(),now();

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from a;

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

| a | b |

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

| 2020-04-20 12:46:44 | 2020-04-20 12:46:44 |

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

1 row in set (0.00 sec)

mysql> select @@time_zone;

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

| @@time_zone |

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

| SYSTEM |

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

1 row in set (0.00 sec)

mysql> set time_zone='+00:00';

Query OK, 0 rows affected (0.00 sec)

mysql> select * from a;

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

| a | b |

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

| 2020-04-20 04:46:44 | 2020-04-20 12:46:44 |

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

1 row in set (0.00 sec)