1. MySQL数据库的特点
MySQL是一个小型的关系型数据库,支持FreeBSD、Linux、MAC、Windows等多种操作系统。MySQL数据库可支持要求最苛刻的Web、电子商务和联机事务处理(OLTP)应用程序。它是一个全面集成、事务安全、符合ACID的数据库,具备全面的提交、回滚、崩溃恢复和行级锁定功能。MySQL凭借其易用性、扩展力和性能,成为全球最受欢迎的开源数据库。
MySQL被设计为一个单进程多线程架构的数据库(通过ps -Lf mysqld pid
或pstack mysqld pid
命令可以查看多线程结构),这点与SQL Server类似,但与Oracle多进程的架构不同(注意Oracle的Windows版本也是单进程多线程架构)。简而言之,MySQL数据库实例在系统上的表现就是一个进程。MySQL主要有以下优点:
• 可以处理拥有上千万条记录的大型数据。
• 支持常见的SQL语句规范。
• 可移植性高,安装简单小巧。
• 良好的运行环境,有丰富信息的网络支持。
• 调试、管理,优化简单(相对其他大型数据库)。
• 复制全局事务标识可支持自我修复式集群。
• 复制无崩溃从机可提高可用性。
• 复制多线程从机可提交性能。
• 对InnoDB进行NoSQL访问,可快速完成键值操作以及快速提取数据来完成大数据部署。
• 在Linux上的性能提升高达230%。
• 在当今的多核、多CPU硬件上具备更高的扩展力。
• InnoDB性能改进,可更加高效地处理事务和只读负载。
• 更快速地执行查询命令,具备增强的诊断功能。
• Performance Schema可监视各个用户和应用程序的资源占用情况。
• 通过基于策略的密码管理和实施来确保安全性。
• 复制功能支持灵活的拓扑架构,可实现向外扩展和高可用性。
• 分区有助于提高性能和管理超大型数据库环境。
• ACID事务支持构建安全可靠的关键业务应用程序。
• Information Schema有助于方便地访问元数据。
• 插入式存储引擎架构可最大限度发挥灵活性。
2. MySQL服务的运行
本文后面的例子均是在Ubuntu环境下,需要在Ubuntu下安装MySQL的可以参考这篇博客。安装完成之后,通过service mysql status
命令来看下MySQL的运行状态,如果是未启动则可以通过service mysql start
命令来进行启动,若要停止服务则使用service mysql stop
。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24root@jack-linux:~# service mysql status
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Sat 2020-11-14 15:02:01 CST; 5s ago
Main PID: 3993 (code=exited, status=0/SUCCESS)
Nov 14 11:19:40 jack-linux systemd[1]: Starting MySQL Community Server...
Nov 14 11:19:40 jack-linux systemd[1]: Started MySQL Community Server.
Nov 14 15:02:00 jack-linux systemd[1]: Stopping MySQL Community Server...
Nov 14 15:02:01 jack-linux systemd[1]: Stopped MySQL Community Server.
root@jack-linux:~# service mysql start
root@jack-linux:~# service mysql status
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2020-11-14 15:02:15 CST; 2s ago
Process: 6982 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
Process: 6960 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 6984 (mysqld)
Tasks: 27 (limit: 2318)
CGroup: /system.slice/mysql.service
└─6984 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
Nov 14 15:02:15 jack-linux systemd[1]: Starting MySQL Community Server...
Nov 14 15:02:15 jack-linux systemd[1]: Started MySQL Community Server.
除上述命令外,还可以通过ps -ef | grep mysql
或netstat -nlp | grep mysql
命令查看MySQL服务的状态。1
2
3
4
5
6root@jack-linux:~# ps -ef | grep mysql
mysql 6984 1 0 15:02 ? 00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
root 7042 6866 0 15:05 pts/0 00:00:00 grep --color=auto mysql
root@jack-linux:~# netstat -nlp | grep mysql
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 6984/mysqld
unix 2 [ ACC ] STREAM LISTENING 3740175 6984/mysqld /var/run/mysqld/mysqld.sock
3. 创建和删除Database
在创建数据库之前,需要登录,这里以root用户登录为例:执行mysql -u root -p
命令后输入设置的密码即可。然后使用SHOW DATABASES;
命令查看MySQL中已有的数据库。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26root@jack-linux:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
创建数据库使用CREATE DATABASE DB_NAME
命令,删除数据库使用DROP DATABASE DB_NAME
。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30mysql> CREATE DATABASE TEST;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> DROP DATABASE TEST;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
如果创建数据库时要指定字符集,则创建时加上CHARACTER SET
关键字即可。然后可以通过SHOW CREATE DATABASE DB_NAME;
命令查看数据库的字符集信息。1
2
3
4
5
6
7
8
9
10
11
12mysql> CREATE DATABASE TEST CHARACTER SET UTF8;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE TEST;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| TEST | CREATE DATABASE `TEST` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
4. 创建和删除Table
创建表使用CREATE TABLE TB_NAME
命令,删除表使用DROP TABLE TB_NAME
。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20mysql> USE TEST;
Database changed
mysql> CREATE TABLE TB_TEST(ID INT NOT NULL, NAME VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_TEST |
+----------------+
| TB_TEST |
+----------------+
1 row in set (0.00 sec)
mysql> DROP TABLE TB_TEST;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql>
同样在创建表时可以指定字符集,加上DEFAULT CHARSET
即可。然后可以使用SHOW CREATE TABLE TB_NAME;
命令查看表的字符集信息。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15mysql> CREATE TABLE TB_TEST(ID INT NOT NULL, NAME VARCHAR(10) NOT NULL) DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW CREATE TABLE TB_TEST;
+---------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------+
| TB_TEST | CREATE TABLE `TB_TEST` (
`ID` int(11) NOT NULL,
`NAME` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5. 查看Table的列名和类型
查看表的列名和类型使用DESC TB_NAME;
命令,或者使用SHOW FULL COLUMNS FROM TB_NAME;
命令,而且后面个命令能够查看的列名信息更全。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19mysql> DESC TB_TEST;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | | NULL | |
| NAME | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> SHOW FULL COLUMNS FROM TB_TEST;
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| ID | int(11) | NULL | NO | | NULL | | select,insert,update,references | |
| NAME | varchar(10) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)
mysql>
其中各个字段的意义:
• Field:字段的名称。
• Type:字段的类型。
• Collation:描述了如何对查询出来的数据进行比较和排序。
• Null:是否允许为空,YES表示允许,NO表示不允许。
• Key:键,表示该列是否有索引,比如:主键(PRI)、唯一键(UNI)、非唯一键或多列唯一键(MUL)等。优先级:PRI > UNI > MUL。
• Default:字段的默认值。
• Extra:附加信息,如自增主键上的(auto_increment)。
• Privileges:权限,有select、insert、update、reference等。
• Comment:字段的注释。
6. 查看创建的索引及索引类型
在查看索引之前,我们先创建一个学生信息表,列名包括学号、姓名、性别、年龄、班级。如果我们要查看索引的具体信息,使用SHOW INDEX FROM TB_NAME;
或者SHOW KEYS FROM TB_NAME;
命令。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42mysql> CREATE TABLE STUDENT(
-> ID INT NOT NULL AUTO_INCREMENT,
-> NAME VARCHAR(10) NOT NULL,
-> GENDER VARCHAR(4) NOT NULL,
-> AGE INT,
-> CLASS VARCHAR(10),
-> PRIMARY KEY(ID),
-> KEY(NAME)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC STUDENT;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | varchar(10) | NO | MUL | NULL | |
| GENDER | varchar(4) | NO | | NULL | |
| AGE | int(11) | YES | | NULL | |
| CLASS | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> SHOW INDEX FROM STUDENT;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| STUDENT | 0 | PRIMARY | 1 | ID | A | 0 | NULL | NULL | | BTREE | | |
| STUDENT | 1 | NAME | 1 | NAME | A | 0 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> SHOW KEYS FROM STUDENT;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| STUDENT | 0 | PRIMARY | 1 | ID | A | 0 | NULL | NULL | | BTREE | | |
| STUDENT | 1 | NAME | 1 | NAME | A | 0 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql>
7. 查看当前数据库中的用户
当前数据库中的用户信息会存储到名为mysql
的数据库中的user
表中,因此我们可以切换到mysql
数据库中,通过SELECT
命令查看user
表中的用户信息。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55mysql> 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 tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> select host, user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
4 rows in set (0.00 sec)
mysql>
8. 查看数据库版本、当前用户和当前数据库
在SELECT
操作后加上VERSION()
函数可以查看数据库版本,使用USER()
函数可以查询当前登录数据库的用户,使用DATABASE()
函数可以获取当前连接的数据库名称。1
2
3
4
5
6
7
8
9mysql> SELECT VERSION(), USER(), DATABASE();
+-------------------------+----------------+------------+
| VERSION() | USER() | DATABASE() |
+-------------------------+----------------+------------+
| 5.7.32-0ubuntu0.18.04.1 | root@localhost | TEST |
+-------------------------+----------------+------------+
1 row in set (0.00 sec)
mysql>
9. MySQL中的数据类型
MySQL中定义数据字段的类型对数据库的优化是非常重要的。MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串类型。
9.1 数值类型
数值类型又可以分为两类:整数类型和带小数的类型。
首先来看下整数类型,以及其大小、包括的数范围和用途。
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 Byte | (-27, 27-1) | (0, 28-1) | 小整数值、微小 |
SMALLINT | 2 Byte | (-215, 215-1) | (0, 216-1) | 大整数值、小 |
MEDIUMINT | 3 Byte | (-223, 223-1) | (0, 224-1) | 大整数值、中等大小 |
INT或 INTEGER |
4 Byte | (-231, 231-1) | (0, 232-1) | 大整数值、普通大小 |
BIGINT | 8 Byte | (-263, 263-1) | (0, 264-1) | 极大整数值、大 |
然后,下面来看下带小数的类型,以及其大小、包括的数范围和用途。
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
FLOAT | 4 Byte | (-3.04028234663551E+38, 1.175494351E-38),0, (1.175494351E-38, 3.402823466351E+38) |
0, (1.175494351E-38, 3.402823466351E+38) |
单精度浮点数值 |
DOUBLE | 8 Byte | (-1.7976931348623157E+308, 2.2250738585072014E-308), 0, (2.2250738585072014E-308, 1.7976931348623157E+308) |
0, (2.2250738585072014 E-308,1.79769313 48623157E+308) |
双精度浮点数值 |
DECIMAL | 对DECIMAL(M,D),若M>D则为M+2 ,否则为D+2 |
依赖于M和D的值 | 依赖于M和D的值 | 小数值、定点数 |
9.2 日期/时间类型
表示时间值的日期/时间类型为:DATETIME、DATE、TIMESTAMP、TIME、YEAR。每个时间类型有一个有效值范围和一个“零”值,当指定不合法的MySQL不能表示的值。
类型 | 大小 | 范围 | 格式 | 用途及注意事项 |
---|---|---|---|---|
DATE | 3 Byte | 1000-01-01 ~ 9999-12-31 |
YYYY-MM-DD | 日期值 |
TIME | 3 Byte | ‘-838:59:59’ ~ ‘838:59:59’ |
HH:MM:SS | 时间值或持续时间 |
YEAR | 1 Byte | 1901-2155 | YYYY | 年份值 |
DATETIME | 5+小数位 | 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999 |
YYYY-MM-DD HH:MM:SS | 混合日期和时间值。DATATIME最大的小数位为6。若小数位为1或2,则总字节数为6(5+1);若小数位为3或4,则总字节数为7(5+2);若小数位为5或6,则总字节数为8(5+3)。 |
TIMESTAMP | 8 Byte | 1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999 |
YYYYMMDDHHMMSS | 混合日期和时间值,时间戳。TIMESTAMP最大小数位是6。 |
使用日期/时间类型需要注意以下两点:
1)如果要记录年月日时分秒,并且记录的年份比较久远,那么最好使用DATETIME,因为其表示范围更广。
2)如果记录的日期需要让不同时区的用户使用,那么使用TIMESTAMP,因为只有它能够和实际的时区相对应。
9.3 字符串类型
字符串类型包括:CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET。各自的大小及用途如下表所示。
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0~28-1 | 定长字符串 |
VARCHAR | 0~216-1 | 变长字符串 |
TINYBLOB | 0~28-1 | 二进制字符串 |
TNIYTEXT | 0~28-1 | 短文本字符串 |
BLOB | 0~216-1 | 二进制形式的长文本数据 |
TEXT | 0~216-1 | 长文本数据、VARCHAR的加长增强版 |
MEDIUMBOLB | 0~224-1 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0~224-1 | 中等长度文本数据 |
LONGBLOB | 0~232-1 | 二进制形式的极大文本数据 |
LONGTEXT | 0~232-1 | 极大文本数据 |
ENUM | 1~2 | 枚举类型 |
SET | 1~8 | 类似于枚举类型,但是SET类型一次可以 选取多个成员,而ENUM只能选一个 |
字符串类型需要注意以下几点:
1)CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。当保存CHAR值,在它们的右边填充空格以达到指定的长度。当检索到CHAR值时,尾部空格的空格会被删除掉,而VARCHAR类型会保留尾部空格。
2)BINARY和VARBINARY类似与CHAR和VARCHAR,不同的是它们只包含二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值。
3)BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、MEDIUMBLOB、BLOB、LONGBLOB。它们的区别只是可容纳值的最大长度不同。
4)有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。这些对应4种BLOB类型,有相同的最大长度和存储需求。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45mysql> CREATE TABLE TB_GENDER(gender enum('F', 'M', 'UN'));
Query OK, 0 rows affected (0.03 sec)
mysql> DESC TB_GENDER;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| gender | enum('F','M','UN') | YES | | NULL | |
+--------+--------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE TB_GENDER2(col set('a', 'b', 'c'), gender enum('F', 'M', 'UN'));
Query OK, 0 rows affected (0.03 sec)
mysql> DESC TB_GENDER2;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| col | set('a','b','c') | YES | | NULL | |
| gender | enum('F','M','UN') | YES | | NULL | |
+--------+--------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO TB_GENDER2 VALUES('d', 'F');
ERROR 1265 (01000): Data truncated for column 'col' at row 1
mysql> INSERT INTO TB_GENDER2 VALUES('a', 'F');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO TB_GENDER2 VALUES('a,c', 'F');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO TB_GENDER2 VALUES('b,c', 'F');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM TB_GENDER2;
+------+--------+
| col | gender |
+------+--------+
| a | F |
| a,c | F |
| b,c | F |
+------+--------+
3 rows in set (0.00 sec)
mysql>
10. MySQL中数据类型的属性
数据类型的属性包括:auto_increment、binary、default、index、null、not null、primary key、unique、zerofill,分别如下表所示:
属性 | 说明 |
---|---|
auto_increment | auto_increment属性能为新插入的行赋予一个唯一的整数标识符,只能用于整数类型。每个表只允许有一个auto_increment列,MySQL要求auto_increment属性列作为主键,并且从1开始依次自动增长。 自动增长列可以手工插入,但插入值是空或者0,那么实际插入的将是自动增长后的值。对于 TRUNCATE 操作,表中的auto_increment属性的值会被置为1,而DELETE 操作不会置为1。 |
binary | binary属性只用于char和varchar值。当为列指定了该属性时,将以区分大小写的方式排序和比较。 |
default | default属性确保在没有任何值可用的情况下,赋予某个常量值 。注意此属性无法用于BLOB和TEXT列,同时在指定null属性的列上,未指定默认值时默认值将为null。 |
index | 为列指定index属性,则会在该列建立索引,以加速数据库的查询。索引一个列会为该列创建一个有序的键数组,每个键指向其相应的表行。以后针对输入条件可以搜索这个有序的键数组,与搜索整个未索引的表相比,性能将会有极大的提升。 |
null | 为列指定null属性时,该列可以保持为空,注意null精确的说法是“无”,而不是空字符串或0,。 |
not null | 如果将列定义为not null,那么将不允许向该列插入null值。通常会在重要列上添加此属性。 |
primary key | primary key属性用于确保指定行的唯一性。主键列中的值不能重复,也不能为空。主键列被赋予auto_increment属性是很常见的,因此此列不必与行数据有任何关系,只是作为唯一的标识符。 |
unique | 被赋予unique属性的列将确保所有值都有不同的值,但是null值可以重复。一般会指定主键列为unique。 |
zerofill | zerofill属性可用于任何数值类型,用0填充所有剩余字段空间。比如,无符号int的默认宽度是10,因此当“零填充”的int值为4时,将表示它为0000000004。 |
下面针对上述的每种类型进行举例说明。
(1)auto_increment1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90mysql> CREATE TABLE TB_AUTO_INC(id smallint auto_increment primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW CREATE TABLE TB_AUTO_INC;
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| TB_AUTO_INC | CREATE TABLE `TB_AUTO_INC` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> DESC TB_AUTO_INC;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | smallint(6) | NO | PRI | NULL | auto_increment |
+-------+-------------+------+-----+---------+----------------+
1 row in set (0.00 sec)
mysql> INSERT INTO TB_AUTO_INC VALUES (null), (5), (0);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM TB_AUTO_INC;
+----+
| id |
+----+
| 1 |
| 5 |
| 6 |
+----+
3 rows in set (0.00 sec)
mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO TB_AUTO_INC VALUES (null);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 7 |
+------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE TB_AUTO_INC1(
-> id1 smallint not null auto_increment,
-> id2 smallint not null,
-> name char(20),
-> index (id2, id1)
-> ) ENGINE=MYISAM; # InnoDB表中要求auto_increment列必须设置为key
mysql> DESC TB_AUTO_INC1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id1 | smallint(6) | NO | | NULL | auto_increment |
| id2 | smallint(6) | NO | MUL | NULL | |
| name | char(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO TB_AUTO_INC1 (id2, name) VALUES (2, 'name2'), (3, 'name3'), (4, 'name4'), (2, 'name2'), (3, 'name3'), (4, 'name4');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM TB_AUTO_INC1;
+-----+-----+-------+
| id1 | id2 | name |
+-----+-----+-------+
| 1 | 2 | name2 |
| 1 | 3 | name3 |
| 1 | 4 | name4 |
| 2 | 2 | name2 |
| 2 | 3 | name3 |
| 2 | 4 | name4 |
+-----+-----+-------+
6 rows in set (0.00 sec)
mysql>
给指定表添加主键列:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53mysql> CREATE TABLE TB_AUTO_INC2(id smallint);
Query OK, 0 rows affected (0.05 sec)
mysql> DESC TB_AUTO_INC2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> ALTER TABLE TB_AUTO_INC2 ADD id0 int auto_increment primary key first;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC TB_AUTO_INC2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id0 | int(11) | NO | PRI | NULL | auto_increment |
| id | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO TB_AUTO_INC2 (id) VALUES (2), (5);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM TB_AUTO_INC2;
+-----+------+
| id0 | id |
+-----+------+
| 1 | 2 |
| 2 | 5 |
+-----+------+
2 rows in set (0.00 sec)
mysql> INSERT INTO TB_AUTO_INC2 VALUES (11, 4), (0, 7);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM TB_AUTO_INC2;
+-----+------+
| id0 | id |
+-----+------+
| 1 | 2 |
| 2 | 5 |
| 11 | 4 |
| 12 | 7 |
+-----+------+
4 rows in set (0.00 sec)
mysql>
修改某个列:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24mysql> CREATE TABLE TB_AUTO_INC3(id smallint);
Query OK, 0 rows affected (0.02 sec)
mysql> DESC TB_AUTO_INC3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> ALTER TABLE TB_AUTO_INC3 MODIFY id int auto_increment primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC TB_AUTO_INC3;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)
mysql>
(2)binary1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30mysql> CREATE TABLE TB_BIN(
-> id int auto_increment primary key,
-> hostname char(25) binary not null
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> DESC TB_BIN;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hostname | char(25) | NO | | NULL | |
+----------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO TB_BIN (hostname) VALUES (123), ('zdsA'), ('4456');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM TB_BIN;
+----+----------+
| id | hostname |
+----+----------+
| 1 | 123 |
| 2 | zdsA |
| 3 | 4456 |
+----+----------+
3 rows in set (0.00 sec)
mysql>
(3)default1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16mysql> CREATE TABLE TB_DEF(
-> id int auto_increment primary key,
-> name varchar(20) default 'test'
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC TB_DEF;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | test | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql>
(4)index1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44mysql> CREATE TABLE PERSON(
-> id int auto_increment primary key,
-> name varchar(20) not null,
-> age int not null default 0,
-> phone varchar(20),
-> index name(name)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> DESC PERSON;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | MUL | NULL | |
| age | int(11) | NO | | 0 | |
| phone | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> SHOW INDEX FROM PERSON;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| PERSON | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| PERSON | 1 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> CREATE INDEX phone_index ON PERSON (phone);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM PERSON;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| PERSON | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| PERSON | 1 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| PERSON | 1 | phone_index | 1 | phone | A | 0 | NULL | NULL | YES | BTREE | | |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql>
(5)zerofill1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40mysql> CREATE TABLE TB_ZERO_FILL(
-> col1 int,
-> col2 int(3),
-> col3 int zerofill,
-> col4 int(3) zerofill
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> DESC TB_ZERO_FILL;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| col1 | int(11) | YES | | NULL | |
| col2 | int(3) | YES | | NULL | |
| col3 | int(10) unsigned zerofill | YES | | NULL | |
| col4 | int(3) unsigned zerofill | YES | | NULL | |
+-------+---------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> SELECT * FROM TB_ZERO_FILL;
+------+------+------------+------+
| col1 | col2 | col3 | col4 |
+------+------+------------+------+
| 1 | 1 | 0000000001 | 001 |
+------+------+------------+------+
1 row in set (0.00 sec)
mysql> INSERT INTO TB_ZERO_FILL VALUES (2, 2, 22, 222);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM TB_ZERO_FILL;
+------+------+------------+------+
| col1 | col2 | col3 | col4 |
+------+------+------------+------+
| 1 | 1 | 0000000001 | 001 |
| 2 | 2 | 0000000022 | 222 |
+------+------+------------+------+
2 rows in set (0.00 sec)
mysql>
11. MySQL中的日期和时间函数
MySQL的日期函数有很多,常用的日期或事件函数如下表所示:
函数 | 功能描述 | 示例 |
---|---|---|
DAYOFWEEK (DATE) |
返回DATE的星期索引 (1=Sunday,···,7=Saturday) |
![]() |
DAYOFYEAR (DATE) |
返回DATE是一年中的 第几天,范围是1~366 |
![]() |
HOUR(TIME)/ MINUTE(TIME)/ SECOND(TIME) |
返回TIME的小时值/ 分钟值/秒值 |
![]() |
DATE_FORMAT (DATE, FORMAT) |
依照FORMAT字符串格式化DATE值,修饰符的含义: %M:月的名字 (January…December) %W:星期的名字 (Sunday…Saturday) %D:有英文后缀的某月的 第几天(1st,2nd,…) %Y:4位数字的年份 %y:2位数字的年份 %m:2位数字的月份 (00…12) %c:不含前缀0的月份 (0…12) %d:2位数字的月份中 的天数(00…31) %e:不含前缀0的月份 中的天数(0…31) %x:4位数字的年份, 其中星期一是星期的第 一天,与“%v”一同使用 %a:缩写的星期名 (Sun…Sat) %b:缩写的月名 (Jan…Dec) %H:2位数字的 24小时(00…23) %k:不含前缀0的 24小时(0…23) %h:2位数字的 12小时(01…12) %I:2位数字的 12小时(01…12) %l:不含前缀0的 12小时(1…12) %i:2位数字的 分钟(00…59) %r:12小时的时间格式 (hh:mm:ss [AP]M) %T:24小时的时间格式 (hh:mm:ss) %S:2位数字的秒 (00…59) %s:2位数字的秒 (00…59) %p:AM或PM %w:一周中的天数 (0=Sunday,···,6=Saturday) |
![]() |
STR_TO_DATE() | 将字符串转换为日期类型 | ![]() |
CURDATE()/ CURRENT_DATE |
以“YYYY-MM-DD”或 “YYYYMMDD”格式 返回当前的日期值 |
![]() |
CURTIME()/ CURRENT_TIME |
以“HH:MM:SS”或 “HHMMSS”格式 返回当前的时间值 |
![]() |
NOW()/ SYSDATE()/ CURRENT_TIMESTAMP |
以“YYYY-MM-DD HH:MM:SS”或 “YYYYMMDDHHMMSS” 格式返回当前的日期 和时间值 |
![]() |
SEC_TO_TIME (NUMBER) |
以“HH:MM:SS”或 “HHMMSS”格式 返回整形参数被转换 后的时分秒值 |
![]() |
TIME_TO_SEC (TIME) |
返回参数TIME被转换 为秒数后的值 |
![]() |
12. MySQL中LIMIT的作用
limit限制返回的结果函数,主要用于查询之后要显示返回的前几条或中间某几行数据,其写法如下所示:LIMIT 0,100;
# 从起始角标为0的位置,往后获取100条记录,可简写为LIMIT 100;
。LIMIT 10,8;
# 从起始角标为10的位置,往后获取8条记录。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45mysql> DESC TB_PERSON;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM TB_PERSON;
+----------+
| COUNT(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM TB_PERSON LIMIT 6;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | test1 | 10 |
| 2 | test2 | 20 |
| 3 | test3 | 30 |
| 4 | test4 | 40 |
| 5 | test5 | 50 |
| 6 | test6 | 60 |
+----+-------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM TB_PERSON LIMIT 3,6;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 4 | test4 | 40 |
| 5 | test5 | 50 |
| 6 | test6 | 60 |
| 7 | test7 | 70 |
| 8 | test8 | 80 |
| 9 | test9 | 90 |
+----+-------+------+
6 rows in set (0.00 sec)
mysql>
limit还可以用于分页操作,但是在数据量和偏移量(offset)比较大时,会变得很低效。所以对limit的优化,要么限制分页的数量,要么降低偏移量的大小。
MySQL中"limit m,n"
的工作原理是先读取前m
条记录,然后抛弃前m
条,再读取n
条想要的记录。所以m
越大,性能会越差。优化思路是,在索引(主键)上完成排序分页的操作,最后根据主键关联回原表查询所需要的其他列内容。示例如下:1
2
3
4
5
6# 待优化的SQL
SELECT * FROM TB_PERSON ORDER BY NAME LIMIT 100, 20;
# 优化后的SQL
SELECT * FROM TB_PERSON INNER JOIN (
SELECT id FROM TB_PERSON ORDER BY NAME LIMIT 100, 20
);
二者区别在于,优化前的SQL需要更多的I/O浪费,因为先读索引,再读数据,然后抛弃无用的行,而优化后的SQL只读索引就可以了,然后通过主键读取需要的列。
13. MySQL中对一张表同时查询和更新
MySQL不允许对同一张表同时进行查询和更新,但可以使用临时表的方式来处理。如下的SQL语句不能正常执行:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46# 原始的数据
mysql> SELECT * FROM TB_PERSON;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | test1 | 10 |
| 2 | test2 | 20 |
| 3 | test3 | 30 |
| 4 | test4 | 40 |
| 5 | test5 | 50 |
| 6 | test6 | 60 |
| 7 | test7 | 70 |
| 8 | test8 | 80 |
| 9 | test9 | 90 |
| 10 | test10 | 100 |
+----+--------+------+
10 rows in set (0.00 sec)
# 目的:将表中所有的name都变为大写
mysql> UPDATE TB_PERSON p
-> SET p.name = (SELECT UPPER(q.name) FROM TB_PERSON q WHERE q.age=p.age);
ERROR 1093 (HY000): You can''t specify target table 'p' for update in FROM clause
# 使用临时表
mysql> UPDATE TB_PERSON p INNER JOIN (SELECT UPPER(name) name, age FROM TB_PERSON) q ON q.age=p.age SET p.name = q.name;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10 Changed: 10 Warnings: 0
mysql> SELECT * FROM TB_PERSON;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | TEST1 | 10 |
| 2 | TEST2 | 20 |
| 3 | TEST3 | 30 |
| 4 | TEST4 | 40 |
| 5 | TEST5 | 50 |
| 6 | TEST6 | 60 |
| 7 | TEST7 | 70 |
| 8 | TEST8 | 80 |
| 9 | TEST9 | 90 |
| 10 | TEST10 | 100 |
+----+--------+------+
10 rows in set (0.00 sec)
mysql>
14. MySQL中在表的指定位置添加列
如果在一个已经建好的表中添加一列,使用ALTER
关键字,用下面的语句实现:1
ALTER TABLE TB_NAME ADD COLUMN NEW_COL_NAME varchar(20) not null;
这条语句会向已有的表中的最后添加新的一列,如果希望添加到指定列的后面,可以使用AFTER
关键字,用下面的语句实现:1
ALTER TABLE TB_NAME ADD COLUMN NEW_COL_NAME varchar(20) not null AFTER TARGET_COL_NAME;
如果想把新列添加到第一列,可以使用FIRST
关键字,用下面的语句实现:1
ALTER TABLE TB_NAME ADD COLUMN NEW_COL_NAME varchar(20) not null FIRST;
可以使用SQL语句ALTER TABLE TB_NAME ADD new_id int auto_increment primary key FIRST;
来添加主键,使用SQL语句ALTER TABLE TB_NAME MODIFY id int auto_increment primary key;
来修改主键列。
15. MySQL中LENGTH和CHAR_LENGTH的区别
LENGTH和CHAR_LENGTH是MySQL中获取字符串长度的两个函数。LENGTH函数是计算字段的长度,单位为字节,1个汉字算3个字节,1个数字或字母算1个字节。CHAR_LENGTH(str)返回的是字符串str的长度,单位为字符。对于CHAR_LENGTH函数而言,一个多字节字符算作一个单字符。
对于Latin1编码字符,这两个函数的返回结果是相同的,但是对于Unicode和其他编码来说,它们的返回结果是不同的。例如,对于一个包含5个2字节字符集的字符串来说,LENGTH()的返回值为10,而CHAR_LENGTH()的返回值为5。1
2
3
4
5
6
7
8
9mysql> SELECT LENGTH(A), LENGTH(B), CHAR_LENGTH(A), CHAR_LENGTH(B) FROM (SELECT _UTF8'你好' A, _GBK'Hi' B) T;
+-----------+-----------+----------------+----------------+
| LENGTH(A) | LENGTH(B) | CHAR_LENGTH(A) | CHAR_LENGTH(B) |
+-----------+-----------+----------------+----------------+
| 6 | 2 | 2 | 2 |
+-----------+-----------+----------------+----------------+
1 row in set (0.00 sec)
mysql>
16. 函数FROM_UNIXTIME和UNIX_TIMESTAMP的作用
函数FROM_UNIXTIME将MySQL中用10位数字存储的时间以日期格式来显示。语法:FROM_UNIXTIME(unix_timestamp, format)
,例如:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17mysql> SELECT FROM_UNIXTIME(987654321, '%Y/%m/%d %H:%i:%s');
+-----------------------------------------------+
| FROM_UNIXTIME(987654321, '%Y/%m/%d %H:%i:%s') |
+-----------------------------------------------+
| 2001/04/19 12:25:21 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(987654321);
+--------------------------+
| FROM_UNIXTIME(987654321) |
+--------------------------+
| 2001-04-19 12:25:21 |
+--------------------------+
1 row in set (0.00 sec)
mysql>
函数UNIX_TIMESTAMP返回指定时间的UNIX格式数字串,即UNIX时间戳(从UTC时间’1970-01-01 00:00:00’开始的秒数),通常为10位数字。语法:UNIX_TIMESTAMP(date)
,参数date
可以是DATE字符串、DATETIME字符串、TIMESTAMP字符串,或是类似于YYMMDD或YYYYMMDD的数字串。
注意:如果传递1个超出范围的时间,那么UNIX_TIMESTAMP()的返回值是0;如果传入的date
是空,那么将返回从UTC时间’1970-01-01 00:00:00’开始到当前时间的秒数。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33mysql> SELECT UNIX_TIMESTAMP('2020-08-15');
+------------------------------+
| UNIX_TIMESTAMP('2020-08-15') |
+------------------------------+
| 1597420800 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('2020-08-15 10:24:05');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-08-15 10:24:05') |
+---------------------------------------+
| 1597458245 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('1234567890');
+------------------------------+
| UNIX_TIMESTAMP('1234567890') |
+------------------------------+
| 0.000000 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1598976033 |
+------------------+
1 row in set (0.00 sec)
mysql>

...
...