MySQL

MySQL，一个开放源码的关系数据库管理系统，读作“My S-Q-L”。MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库。但被甲骨文公司收购后，大幅调涨MySQL商业版的售价，且甲骨文公司不再支持另一个自由软件项目OpenSolaris的发展，MySQL的创始人麦克尔·维德纽斯以MySQL为基础，成立分支计划MariaDB。而原先一些使用MySQL的开源软件逐渐转向MariaDB或其它的数据库。例如维基百科已于2013年正式宣布将从MySQL迁移到MariaDB数据库。

简介

时间轴

  • 2008年1月16日，Sun（太阳微系统）正式收购MySQL。
  • 2009年4月20日，甲骨文公司宣布以每股9.50美元，74亿美元的总额收购Sun电脑公司。
  • 2013年6月18日，甲骨文公司修改MySQL授权协议，移除了GPL。但随后有消息称这是一个bug。

安装

使用Docker安装

安装Docker，然后直接从官网镜像启动一个mysql容器，并设置root用户密码为123456。 

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

了解更多 >> Docker Hub：MySQL


Ubuntu上安装

#更新软件源
sudo apt-get update 

#
sudo apt-get install mysql-server

了解更多 >> MySQL文档：使用APT仓库安装


可能遇到问题

  • 主机只有1G内存，mysql却占用很大内存。

在终端输入top查看各程序cup和内存等占用率，然后输入M，使结果按内存大小排序，可以看到mysqld的占用率高。直接修改mysql服务器的配置文件my.cnf，如果使用mysql容器，先进入容器终端。 

#进入某个容器
docker exec -it some-mysql bash
#在容器内安装vim
apt-get update
apt-get install vim

#打开并修改my.cnf文件
vim /etc/mysql/my.cnf
#在文件里加入如下配置
performance_schema_max_table_instances=400  
table_definition_cache=400  
table_open_cache=256
performance_schema = off

#重启mysql
service mysql stop
service mysql start
#输入exit，退出容器，然后重启该容器
docker restart some-mysql 
#用top命令查看内存占用率
  • 错误：ERROR 1698 (28000): Access denied for user 'root'@'localhost'

基础知识

MySQL架构

drawio: MySQL架构

了解更多 >> MySQL 文档：MySQL 存储引擎架构概述 Oreilly：高性能MySQL - 第1章 MySQL架构和历史


客户端

名称 界面 描述
MySQL客户端程序 命令 免费。
MySQL客户端程序包含：
mysql 命令行客户端
mysqldump 数据库备份程序
mysqladmin 服务器管理程序
...
Navicat 图形 收费，简单易用，可用于多种数据库管理
SQLyog 图形 收费，简单易用
DBeaver 图形 免费，开源
MySQL Workbench 图形 免费，开源，MySQL官方工具
VS Code插件MySQL 图形 免费，开源。一款VS Code中的插件，作者cweijan。支持多种数据库，使用简单方便。

了解更多 >> MySQL 文档：MySQL程序 - 客户端程序


数据类型

概览

类型

类别 类型 描述 示例
字符串 CHAR(n) 固定长度字符串。 CHAR(10)
VARCHAR(n) 可变长度字符 VARCHAR(150)
TINYTEXT 字符串大对象
TEXT 字符串大对象
MEDIUMTEXT 字符串大对象
LONGTEXT 字符串大对象
ENUM 枚举
SET 集合
二进制字符串 BINARY(n) 二进制 BINARY(10)
VARBINARY(n) 可变长度二进制 VARBINARY(200)
TINYBLOB 二进制大对象
BLOB 二进制大对象 BLOB
MEDIUMBLOB 二进制大对象
LONGBLOB 二进制大对象
准确数值 TINYINT 微型整数，非标准SQL。占1字节，范围：无符号0~255，有符号-128~127。MySQL没有定义布尔类型（BOOL,BOOLEAN），使用TINYINT(1)表示，0表示false，非零表示true。
SMALLINT 小型整数，非标准SQL。占2字节， SMALLINT
MEDIUMINT 中型整数，非标准SQL。占3字节， MEDIUMINT
INTEGER, INT 整数，占4字节，范围：无符号0~4294967295，有符号-2147483648~2147483647 INTEGER
BIGINT 大型整数，非标准SQL。占8字节，范围：无符号0~ 2^64-1，有符号-2^63~2^63 BIGINT
NUMERIC(p,s) 带固定精度和小数位数的数值。p（precision）表示整数和小数的总位数，s（scale）表示小数位数，p和s都可以省略。 NUMERIC(5,2)
DECIMAL(p,s) 带固定精度和小数位数的数值，p（precision）表示整数和小数的总位数，s（scale）表示小数位数，p和s都可以省略。 DECIMAL(5,2)
近似数值 FLOAT(p) 浮点数 FLOAT(3)
REAL 单精度浮点数 REAL
DOUBLE 双精度 DOUBLE
日期时间 DATE[(p)] 日期。默认显示YYYY-MM-DD，范围1000-01-01~9999-12-31 DATE
TIME(p) 时间 TIME
DATETIME[(p)] 日期和时间。支持的范围是 '1000-01-01 00:00:00.000000'到 '9999-12-31 23:59:59.999999'。 DATE
TIMESTAMP[(p)] 时间戳 TIMESTAMP(9)
YEAR[(p)] 年。默认显示YYYY YEAR
JSON JSON JSON
空间数据 geom 空间数据


了解更多 >> MySQL 文档：数据类型 MySQL 文档：使用来自其他数据库引擎的数据类型


转换

MySQL可以自动转换其他数据库的某些数据类型到MySQL的数据类型。可以在表定义时使用这些类型，如： 

-- 定义一个表t1
CREATE TABLE t1 (id int3, is_ok BOOL);

-- 查看数据表定义可知，已经转换为MySQL类型
desc t1;
其他数据库类型 MySQL 类型
BOOL TINYINT
BOOLEAN TINYINT
CHARACTER VARYING(M) VARCHAR(M)
FIXED DECIMAL
FLOAT4 FLOAT
FLOAT8 DOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
LONG MEDIUMTEXT
MIDDLEINT MEDIUMINT
NUMERIC DECIMAL

了解更多 >> MySQL 文档：使用来自其他数据库引擎的数据类型


SQL语句

基础语法

Mysql的SQL语句与标准的SQL语句类似：

  • 不区分大小写，除固定的字符串值。但一般SQL关键词大写，其他列名和表名等小写，这样易于阅读。
  • 语句以分号 ; 结束。
  • 注释
-- 单行注释
SELECT * FROM 表名;

/* 
多行注释
多行注释
*/
SELECT * FROM 表名;

数据定义 DDL

数据库

名称 常用语句 描述 示例
创建数据库
create database 		create database 数据库名；

create database if not exists 数据库名；

create database 数据库名 character set 字符集； 		创建指定名称的数据库。character set 用于指定字符集，SHOW CHARACTER SET语句显示所有可用的字符集。

创建一个数据库，MySQL会在数据目录下创建一个同名的目录。MySQL 对数据库的数量没有限制。 		CREATE DATABASE db1; 创建名称为db1的数据库。
CREATE DATABASE db2 CHARACTER SET utf8; 创建数据库db2，并指定utf8字符集。
查看所有数据库
show databases 		show databases;

show databases like '模式'; 		显示MySQL中的数据库。 show databases; 显示所有数据库。
show databases like 'my%'; 显示所有以my开头的数据库。
使用数据库
use 		use 数据库; 当前语句执行的默认数据库。

如USE db1;接下来所有操作默认在db1数据库中，SELECT * FROM table1;是从db1.table1表查询，要使用其他数据库的表需要指定数据库名如db2.table1。 		USE db2; 切换到db2数据库。
查看当前数据库 select database(); 查看当前语句执行的默认数据库。 select database();
查看数据库定义 show create database 数据库； 查看一个数据库的定义信息。 SHOW CREATE DATABASE db1;
修改数据库
alter database 		alter database 数据库 character set 字符集；

alter database 数据库 read only = {DEFAULT | 0 | 1} 		修改数据库。

character set 修改字符集
read only 修改是否只读。8.0.22版本新增。 		ALTER DATABASE db1 CHARACTER SET utf8; 修改数据库db1的字符集为utf8。
ALTER DATABASE db1 READ ONLY = 1 修改数据库db1为只读。
删除数据库
drop database 		drop database 数据库;
drop database if exists 数据库; 		删除数据库。需要非常小心，所有该数据库及其所有表都将删除。if exists 可防止数据库不存在而报错。 DROP DATABASE db1;

了解更多 >> MySQL 文档：数据定义语句 MySQL 文档：教程 - 创建和使用数据库


数据表

名称 语句 描述 示例
创建表 
create table 表名(
列名1 数据类型， 
列名2 数据类型 
)；

create table  [if not exists] 表名(
  列定义,
  ...,
  表约束,
  ...,
)
表选项,
...,
分区选项,
...;
默认情况下，使用InnoDB存储引擎在当前默认数据库中创建表，MySQL对表的数量没有限制。

常用列定义：
1.列名
2.数据类型，如CHAR(20)。
3.null或 not null，能不能为空值，默认可以NULL。
4.default 默认值，如DEFAULT 0， 8.0.13+默认值可以常量或用括号包裹(表达式)
5.AUTO_INCREMENT，自增长，每个表只能有一个自增长列且该列添加索引。6.COMMENT 注释，给列添加注释，最长1024 个字符。使用SHOW CREATE TABLE和SHOW FULL COLUMNS语句时会显示。
7.primary key，主键，一个表只有一个。该列必须约束有UNIQUE和NOT NULL，没有声明时MySQL会自动声明。
8.index 或 key，普通索引。
9.unique，唯一索引。
10.fulltext，全文索引。
11.foreign key，外键。
12.check，检查约束。

常用表选项：
1.engine，存储引擎，默认InnoDB，设置其他如ENGINE MEMORY
2.CHARACTER SET，指定表的字符集
3.COMMENT，表的注释。

常用分区选项
1. 
CREATE TABLE book (
book_id INT UNSIGNED,
book_name VARCHAR(50)
);
快速创建空表 create table 新表名 like 旧表名; 根据一个表的定义创建一个空表。只能用于创建普通表，不能用于视图。 CREATE TABLE t2 LIKE t1;
CREATE TABLE IF NOT EXISTS t2 LIKE t1;
使用SELECT创建表 create table 新表名 [as] select语句 旧表名; 在CREATE TABLE语句的末尾添加SELECT语句，创建一个表。 
CREATE TABLE t2 SELECT * FROM t1;

CREATE TABLE t2 (
a INT, 
c INT )
SELECT * FROM FROM t1;
创建临时表 create temporary table 表名 ...; 临时表只在当前会话中可见，当会话关闭时自动删除。 CREATE TEMPORARY TABLE t2 SELECT * FROM t1;
查看所有表 show tables; 查看当前数据库中的所有表名。 show tables;
查看所有表详细信息 show table status; 与show tables类似，但提供了大量与表相关的信息，而不只是表名，如存储引擎、行数、创建时间和更新时间等。 show table status;
show table status like 't%';
查看表中列的信息 desc 表名；
describe 表名; 		查看表中列的信息。 DESC t1;
查看表中列的信息 show columns from 表名;
show [extended] [full] columns from 表名; 		查看表中列的信息，比desc语句显示更多信息。extended，可以输出MySQL内部使用的隐藏信息。full，可以输出列排序规则，注释，当前用户权限。 SHOW COLUMNS FROM t1;
SHOW COLUMNS FROM db2.t1;
SHOW FULL COLUMNS FROM t1 FROM db2;
查看建表语句 show create table 表名; 查看表的create table语句, SHOW CREATE TABLE t1;
修改表名 rename table 旧表名 to 新表名； 修改表名 RENAME TABLE t1 TO new_t1;
RENAME TABLE old_t1 TO new_t1, old_t2 to new_t2;
增加列和删除列 
alter table 表名 add [column] 列名 数据类型；

alter table 表名 drop [column] 列名；
添加列add，删除列drop，column关键字可以省略。 ALTER TABLE t1 DROP name VARCHAR(20); 向t1表增加name列
ALTER TABLE t1 DROP COLUMN a, DROP COLUMN b;
修改列名称或定义 alter table 表名 change 旧列名 新列名 列定义; 可以同时修改列名或列定义 ALTER TABLE t1 CHANGE a c INT NOT NULL; 修改a列名称为c，并修改列定义。
修改列定义 alter table 表名 modify 旧列名 列定义; 只修改列定义 ALTER TABLE t1 MODIFY a INT NOT NULL;
修改列名 alter table 表名 rename column 旧列名 to 新列名; 只修改列名称 ALTER TABLE t1 RENAME COLUMN a TO c;
修改字符集 
alter table 表名 convert to character set 字符集；

alter table 表名 modify 列名 数据类型 character set 字符集；

alter table 表名 [default] character set 字符集；
convert to用于修改所有列字符集。
modify或change修改单列字符集。
default仅修改默认字符集，对后面添加列有效。 		ALTER TABLE t1 MODIFY a TEXT CHARACTER SET utf8;
快速清空表 truncate [table] 表名 快速清楚一个表，并创建一个相同空表。 TRUNCATE TABLE t1

了解更多 >> MySQL 文档：数据定义语句 MySQL 文档：教程 - 创建和使用数据库


数据操作 DML

插入数据

名称 语句 描述 示例
插入数据 
insert into 表名 (列名列表) values (值列表);

insert into 表名 (列名列表) values (值列表), (值列表), ...;
可以插入一行或多行数据。列名列表要和值列表对应，列名列表可以是表的部分列，其他未列出填充默认值。 INSERT INTO t1 (a) VALUES (1);
INSERT INTO t1 (a,b,c) VALUES (2,"hi",10)
完整列插入数据 
insert into 表名 values (值列表);

insert into 表名 values (值列表),(值列表),...;
省略列名列表，但值列表要与表结构一致，不能省略。 INSERT INTO t1 VALUES (3,"hello",20)
SELECT语句插入数据 
insert into 表名 [(列名列表)] select语句;
select语句结果插入指定表。 INSERT INTO t2 (a) SELECT t1.a from t1 where t1.a > 60;

了解更多 >> MySQL 文档：INSERT 语句 MySQL 文档：INSERT ... SELECT 语句


更新数据

名称 语句 描述 示例
更新整列数据 
update 表名 set 列名 = 值;

update 表名 set 列名 = 值 order by 子句;
不带where语句，更新一列数据。可以使用oder by子句排序后更新，如想要主键的列+1，使用order降序，先更新最大的值就不会报重复值错误。 UPDATE t1 SET a = a + 1; 表t1的a列值+1
UPDATE t1 SET b = 'hi', c = c + 1; 表t1的b列改为'hi',c列+1
UPDATE t3 SET id = id + 1 order by id desc; 表t3的id列值+1
条件更新数据 
update 表名 set 列名 = 值 where 语句;
只修改列中符合条件的行。 UPDATE t1 SET a = a + 1 WHERE a > 10; 表t1的a列中大于10的都+1
查询更新数据 
update 表名，
  (select 子句) as 子句表名 
set 表名.列名 = 值 
where 表名.列名 = 子句表名.列名;

了解更多 >> MySQL 文档：UPDATE 语句


删除数据

名称 语句 描述 示例
删除所有数据 delete from 表名; 删除指定表所有行。快速删除使用truncate table语句，该语句是删除整个表，再创建一个同样的空表。 delete from t1; 删除t1表的所有数据。
条件删除数据 
delete from 表名 where 语句;
按条件删除行 delete from t1 where a > 10; 删除t1表中a>10的行。
多表删除数据

了解更多 >> MySQL 文档：delete 语句


数据查询 DQL

书写和执行顺序

SQL中最常见的操作是查询，它是通过SELECT语句执行的。SELECT语句从一个或多个的表或视图中检索数据。 常用SELECT语句书写顺序如下，其中括号里的数字表示执行顺序： 

(7)  SELECT    (8) DISTINCT <显示内容>   
(1)  FROM <左表名>       
(3)  <连接类型> JOIN <右表名>
(2)            ON <连接条件>
(4)  WHERE  <筛选条件>     
(5)  GROUP BY  <分组>
(6)  HAVING <分组后筛选条件>
(9)  ORDER BY <排序>
(10) LIMIT <限制与偏移量>;


了解更多 >> MySQL 文档：select 语句 《MySQL技术内幕：SQL编程》：第三章 查询处理 - 3.1 逻辑查询处理


基本查询

查询数据 语句 描述 示例
查询所有列 
select * from 表名;
通配符*，表示表中所有列。 SELECT * FROM book;
查询单列 
select 列名 from 表名;
SELECT id FROM book;
查询多列 
select 列名1, 列名2, 列名3 from 表名;
列名用逗号隔开，最后一个列名不加逗号。 SELECT name, birth FROM person;
设置别名 
select 列名 as 别名 from 表名;

select 列名1 别名1, 列名2 别名2 from 表名;
有时为列名和表名设置别名，可以显示更清楚，方便引用。
显式设置别名的关键字AS可以省略，但不建议，使用AS增加了可读性。 		SELECT name AS n FROM book ORDER BY n;
去除重复值 
select distinct 列名 from 表名;

select distinct 列名1, 列名2, ... from 表名;
可以查询一列不同的值，或多列组合不同的值。distinct关键词要置于列名的开头。 SELECT DISTINCT name FROM book;
SELECT DISTINCT id, name FROM book;
SELECT count( DISTINCT id, name ) FROM book;
限制返回行数 
select * from 表名 limit 行数;

select * from 表名 limit 偏移量,行数;

select * from 表名 limit 行数 offset 偏移量;
LIMIT子句用于限制SELECT语句返回的行数。默认偏移量为0，表示结果集开头开始返回，即limit 行数和limit 0, 行数一样。limit 1, 5表从结果集的第2行开始，选取5行。为了与 PostgreSQL 兼容，MySQL 也支持 LIMIT 行数 OFFSET 偏移量 语法。

排序

ORDER BY子句用于对一列或多列进行排序。

名称 语句 描述 示例
单列排序 
select * from 表名
order by 列名;

select * from 表名
order by 列名 desc;
升序，使用ASC关键字，默认方式，关键字可以省略
降序，使用DESC关键字
多列排序 
select * from 表名
order by 列名1 desc, 列名2;
先按第一列排序，当第一列相同时再按第二列排序。

条件筛选

WHERE子句用于条件筛选查询。

名称 语句 描述 示例
比较函数和比较运算符 
select * from 表名 
where 列名 = 值;
常见比较函数和比较运算符：
= 等于
<>, != 不等于
>, >= 大于，大于等于
<, <= 小于，小于等于
BETWEEN 最小值 AND 最大值 最小值和最大值之间，包含边界。
IN(集合) 是否在一组值内
IS TRUE, FALSE, or UNKNOWN 是否为某个布尔值
IS NOT TRUE, FALSE, or UNKNOWN 是否不为某个布尔值
IS NULL 是否为空值
IS NOT NULL是否不为空值
ISNULL(表达式)
LIKE 简单模式匹配，可以使用2个通配符，%匹配0个或多个字符，_匹配一个字符。
逻辑运算符 
select * from 表名 
where 列名1 = 值1 
  and 列名2 = 值2;
逻辑运算符可以将多个查询条件组合。

Mysql中的逻辑运算符：
AND, && 逻辑与
NOT, ! 逻辑非
OR, || 逻辑或
XOR 逻辑异或

了解更多 >> MySQL 文档：比较函数和比较运算符


分组

GROUP BY子句可以对一列或多列进行分组，常用聚合函数一起使用实现分组统计。可以使用HAVING子句进行分组统计后条件筛选。

常用聚合函数：

聚合函数 描述 示例
AVG() 平均值 
select 列名1, avg(列名2) from 表名
group by 列名1
MAX() 最大值
MIN() 最小值
SUM() 总和
STD() 总体标准差

了解更多 >> MySQL 文档： 聚合函数


多表查询

多表查询，一般通过JOIN关键字将表建立连接，再查询。

分类 命令 描述 示例
交叉连接查询 
SELECT 列名 FROM 左表, 右表;
交叉连接（Cross Join），两个表之间产生笛卡尔积，即左表的每一行与右表的每一行连接，返回大量行（两表行数的乘积）。
内连接查询 
SELECT 列名 
FROM 左表
[INNER] JOIN 右表 
  ON 条件;
按条件取两表交集，使用inner join ...on 语句，inner可省略。
隐式内链接查询 
SELECT 
列名 
FROM 左表, 右表 
WHERE 连接条件;
结果与内连接查询一样。
左外连接 
SELECT 列名 
FROM 左表  
LEFT [OUTER] JOIN 右表 
  ON 条件;
以左表为基准，左边所有数据，以及右表与左表有交集部分。对于左表有而右表没有的数据，右边显示NULL。OUTER可省略。
右外连接 
SELECT 列名 
FROM 左表  
RIGHT [OUTER] JOIN 右表 
  ON 条件;
以右表为基准，右边所有数据，以及左表与右表有交集部分。对于右表有而左表没有的数据，左边显示NULL。OUTER可省略。与左外连接相似，一般使用左外连接即可。

了解更多 >> MySQL 文档：JOIN 子句


查询合并

合并多条SELECT语句的结果集使用UNION关键字。两个SELECT语句的结果列数需要相同，数据类型相似。合并结果集的列名同第一个SELECT语句，数据类型长度同最长的SELECT语句。

名称 语句 描述 示例
合并，删除重复行
UNION 
SELECT ...
UNION [DISTINCT]
SELECT ...
UNION默认删除重复行，可以省略显示指定的关键字DISTINCT。
合并，不删除重复行
UNION ALL 
SELECT ...
UNION ALL
SELECT ...
使用关键字UNION ALL，保留所有重复行。因为不用删除重复行，UNION ALL速度会比UNION速度快。

了解更多 >> MySQL 文档：UNION 子句


子查询

子查询是SELECT语句中嵌套的SELECT语句，子查询中也可以嵌套子查询。子查询必须在括号内，子查询的返回可以是单个值、单行、单列和表。子查询比JOIN和UNION更易读和调试，对于复杂语句，子查询可以逐步测试SELECT语句。



了解更多 >> MySQL 文档：子查询


数据控制 DCL

函数和运算符

函数概览

使用函数可以方便进行各种计算、转换等数据处理。MySQL内置了很多函数。

默认情况下，函数和它后面括号之间不能有空格，函数的参数可以有空格。

了解更多 >> MySQL 文档：函数和运算符


数值函数和运算符

名称 描述 示例
+, -, *, / 加，减，乘，除运算符 select 1+1; 返回2
- 更改符号 select -2; 返回-2
%, MOD 返回余数运算符 select 7 % 4; 7除以4的余数，即3
select 7 mod 4;
MOD() 返回余数函数
DIV 整除，返回整数丢弃小数。 select 3 div 2; 返回1
ABS() 返回绝对值
CEIL(), CEILING() 向上取整，返回不小于参数的最小整数值 select ceil(2.5); 返回3
FLOOR() 向下取整，返回不大于参数的最大整数值 select floor(2.5); 返回2
ROUND(X), ROUND(X,D) 四舍五入，参数D指定小数位数，默认为0。
RAND(), RAND(N) 返回一个0~1的随机数，左闭右开。可以指定一个整数参数作为种子值，将每次返回一组相同的随机值。 select rand();
SELECT FLOOR(2 + (RAND() * 3));返回>=2且<5的随机整数
select rand(3);
PI() 返回 pi 的值
SIN()、COS()、COT()、TAN() 返回参数的正弦、余弦、余切、正切
ASIN()、ACOS()、ATAN() 返回反正弦、反余弦、反正切
ATAN2(),ATAN() 返回两个参数的反正切
CONV() 在不同数字基数之间转换数字
CRC32() 计算循环冗余校验值
DEGREES() 将弧度转换为度
EXP() 提升到权力
LN() 自然数e为底的对数
LOG() 返回第一个参数的自然对数
LOG2()、LOG10() 返回参数的以 2或10 为底的对数
POW() 将引发的参数返回到指定的幂
POWER() 将引发的参数返回到指定的幂
RADIANS() 返回参数转换为弧度
SIGN() 返回参数的符号
SQRT() 返回参数的平方根
TRUNCATE() 截断到指定的小数位数

了解更多 >> MySQL 文档：数值函数和运算符


字符串函数

名称 描述 示例
CONCAT(s1,s2,...) 拼接字符串
CONCAT_WS(separator,s1,s2,...) 带分隔符拼接字符串
LEFT(s,n) 返回字符串s最左边的n个字符。
RIGHT(s,n) 返回字符串s最右边的n个字符。
MID(s,p,n), SUBSTRING(),SUBSTR() 返回字符串s从第p个位置开始的n个字符。3个函数都是一样。
TRIM() 删除字符串开头和结尾的空格
LTRIM()、RTRIM() 删除字符串开头或结尾的空格
REVERSE(s) 反转字符串中的字符 SELECT REVERSE('abc'); 返回'cba'
REPEAT(s, n) 重复一个字符串指定的次数 SELECT REPEAT('ab', 3); 返回'ababab'
LOWER(), LCASE() 转换为大写
UPPER(), UCASE() 转换为大写
LENGTH() 返回字符串的长度（以字节为单位）
REPLACE(s, from_s, to_s) 字符串替换
ASCII() 返回最左边字符的数值
BIN() 返回一个包含数字二进制表示的字符串
BIT_LENGTH() 返回参数的长度（以位为单位）
CHAR() 返回传递的每个整数的字符
CHAR_LENGTH() 返回参数中的字符数
CHARACTER_LENGTH() CHAR_LENGTH() 的同义词
ELT() 返回索引号处的字符串
EXPORT_SET() 返回一个字符串，这样对于值位中设置的每个位，您都会得到一个 on 字符串，对于每个未设置的位，您都会得到一个 off 字符串
FIELD() 后续参数中第一个参数的索引（位置）
FIND_IN_SET() 第二个参数中第一个参数的索引（位置）
FORMAT() 返回格式化为指定小数位数的数字
FROM_BASE64() 解码base64编码字符串并返回结果
HEX() 十进制或字符串值的十六进制表示
INSERT() 在指定位置插入子字符串，最多指定字符数
INSTR() 返回子字符串第一次出现的索引
LIKE 简单的模式匹配
LOAD_FILE() 加载命名文件
LOCATE() 返回子字符串第一次出现的位置
LPAD() 返回字符串参数，左填充指定的字符串
MAKE_SET() 返回一组逗号分隔的字符串，这些字符串在 bits 集中具有相应的位
MATCH() 执行全文搜索
NOT LIKE 简单模式匹配的否定
NOT REGEXP 否定正则表达式
OCT() 返回包含数字的八进制表示的字符串
ORD() 返回参数最左边字符的字符代码
POSITION() LOCATE() 的同义词
QUOTE() 转义参数以在 SQL 语句中使用
REGEXP 字符串是否匹配正则表达式
REGEXP_INSTR() 子串匹配正则表达式的起始索引
REGEXP_LIKE() 字符串是否匹配正则表达式
REGEXP_REPLACE() 替换匹配正则表达式的子字符串
REGEXP_SUBSTR() 返回匹配正则表达式的子字符串
RLIKE 字符串是否匹配正则表达式
RPAD() 附加字符串指定次数
SOUNDEX() 返回一个 soundex 字符串
SOUNDS LIKE 比较声音
SPACE() 返回指定空格数的字符串
STRCMP() 比较两个字符串
SUBSTRING_INDEX() 从指定出现次数的分隔符之前的字符串中返回子字符串
TO_BASE64() 返回转换为 base-64 字符串的参数
UNHEX() 返回包含数字的十六进制表示的字符串
WEIGHT_STRING() 返回字符串的权重字符串

了解更多 >> MySQL 文档：字符串函数和运算符


日期和时间函数

名称 描述 示例
CURDATE(),CURRENT_DATE(),CURRENT_DATE 返回当前日期
CURTIME(),CURRENT_TIME(),CURRENT_TIME 返回当前时间
NOW(), LOCALTIME(), LOCALTIME, LOCALTIMESTAMP(), LOCALTIMESTAMP, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP 返回当前日期和时间
UTC_DATE() 返回当前 UTC 日期
UTC_TIME() 返回当前 UTC 时间
UTC_TIMESTAMP() 返回当前 UTC 日期和时间
UNIX_TIMESTAMP() 返回一个 Unix 时间戳
DATE() 提取日期
YEAR() 返回年份
DAY(),DAYOFMONTH() 返回月份中的第几天 (0-31)
EXTRACT(unit FROM date) 提取日期或时间的某一部分， SELECT EXTRACT(YEAR FROM '2021-02-21'); 返回2021
DATEDIFF() 两个日期相减
TIMEDIFF() 两个时间相减或两个日期和时间相减。
ADDDATE() 将时间值（间隔）添加到日期值
ADDTIME() 添加时间
CONVERT_TZ() 从一个时区转换到另一个时区
DATE_ADD() 将时间值（间隔）添加到日期值
DATE_FORMAT() 按指定格式日期
DATE_SUB() 从日期中减去时间值（间隔）
DAYNAME() 返回工作日的名称
DAYOFWEEK() 返回参数的工作日索引
DAYOFYEAR() 返回一年中的某一天 (1-366)
FROM_DAYS() 将天数转换为日期
FROM_UNIXTIME() 将 Unix 时间戳格式化为日期
GET_FORMAT() 返回日期格式字符串
HOUR() 提取小时
LAST_DAY 返回参数的月份的最后一天
MAKEDATE() 根据年份和日期创建日期
MAKETIME() 从小时、分钟、秒创建时间
MICROSECOND() 从参数返回微秒
MINUTE() 从参数返回分钟
MONTH() 从过去的日期返回月份
MONTHNAME() 返回月份的名称
PERIOD_ADD() 为年月添加期间
PERIOD_DIFF() 返回期间之间的月数
QUARTER() 从日期参数返回季度
SEC_TO_TIME() 将秒转换为 'hh:mm:ss' 格式
SECOND() 返回第二个 (0-59)
STR_TO_DATE() 将字符串转换为日期
SUBDATE() 使用三个参数调用时 DATE_SUB() 的同义词
SUBTIME() 减去时间
SYSDATE() 返回函数执行的时间
TIME() 提取传递的表达式的时间部分
TIME_FORMAT() 格式为时间
TIME_TO_SEC() 返回转换为秒的参数
TIMESTAMP() 使用单个参数，此函数返回日期或日期时间表达式；有两个参数，参数的总和
TIMESTAMPADD() 向日期时间表达式添加间隔
TIMESTAMPDIFF() 从日期时间表达式中减去间隔
TO_DAYS() 返回转换为天的日期参数
TO_SECONDS() 返回自第 0 年以来转换为秒的日期或日期时间参数
WEEK() 返回周数
WEEKDAY() 返回工作日索引
WEEKOFYEAR() 返回日期的日历周 (1-53)
YEARWEEK() 返回年份和星期


了解更多 >> MySQL 文档：日期和时间函数


索引

优化

常用工具

导入导出

导入

导入工具 支持格式 描述
LOAD DATA 语句 CSV等 SQL语句 LOAD DATA用于加载数据文本类型数据。示例：

当需要导入大量本地数据到mysql数据库时，可使用LOAD DATA LOCAL INFILE命令。但local_infile选项默认关闭状态，需要打开。以下以导入5千万行CSV文件为例。 
-- mysql8.0以上版本，命令行登录数据库时，使用如下命令
-- mysql -u root -p --local-infile

-- 查看'local_infile'选项，并设置为ON
show variables like 'local_infile';
set global local_infile=on;

-- 建test表
CREATE TABLE test( 
  user_id INT, 
  name VARCHAR);

-- 导入本地csv文件，如果第一行有标题使用IGNORE 1 LINES忽略
LOAD DATA LOCAL INFILE 'C:\\Users\\TESTWORK\\Desktop\\test_data.csv'
INTO TABLE test
FIELDS TERMINATED BY ','
IGNORE 1 LINES;
mysqlimport CSV等 mysqlimport工具是LOAD DATA 语句的命令行接口
mysqldump SQL语句 mysqldump工具用于备份，可以导出和导入sql语句。

导出

常见问题

使用LOAD DATA INFILE加载CSV文件时，空值（NULL）会自动变为0。

空值和0不同，如在计算均值时，空值不会纳入计算，0值会增加个数。两种方法：

  • 方法1：导入时使用NULLIF()函数进行预处理
-- 示例，对name列的空值进行预处理
-- 也可以全部例进行预处理，但最后一列好像不起作用?

LOAD DATA INFILE '/data/testdata.csv'
INTO TABLE book
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, @name, author) 
SET name = NULLIF(@name,'')
;
  • 方法2：对csv文件进行预处理，空值处插入转义字符\N会自动转为NULL，如某行15, ,10，改为15,\N,10

了解更多 >> stackoverflow：MySQL从CSV数据加载NULL值 MySQL 文档：空值的问题 MySQL 文档：Load Data 语句 


LOAD DATA INFILE时报错。 
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

SHOW VARIABLES LIKE "secure_file_priv";

  • 方法1：查询secure_file_priv值 SHOW VARIABLES LIKE "secure_file_priv"; ，如果存在目录，将文件移动到该安全目录即可导入。
  • 方法2：配置secure_file_priv值，如ubuntu中可以编辑/etc/mysql/my.cnf配置文件，设置secure-file-priv= /data，重启mysql服务即可，docker可以直接重启容器。将文件放入/data目录即可导入。

了解更多 >> stackoverflow：MySQL ERROR 1290 (HY000) --secure-file-priv option


资源

官网

书籍

教程

网页

取自“https://zxdd.com/index.php?title=MySQL&oldid=974”