Mysql基础命令及语法笔记

2024/7/15·1 views

show 命令

help show 查看允许的show语句

sql 复制代码

show databases
show tables
show [full] columns from <table>
show create database/table <name>
show status
show grants
show triggers # 查看触发器
show grants for `username`
show errors
show warnings

set names

设置编码

复制代码

set names 'utf8';

select

sql 复制代码

select distinct <column> from <table>

对于order by , A 和 a 不一定相同（在mysql中默认相同【字典排序】），取决于数据库怎么设计，通常可以改变这种行为。

对于没有排序的语句，返回的结果集的顺序没有特殊意义，又可能按照插入表的顺序，也有可能不是，只要返回总数正确，就是正常的。

正则

sql 复制代码

SELECT * FROM users WHERE name REGEXP BINARY '^cheng.*g$' --使用BINARY来区分大小写

sql 复制代码

SELECT * FROM information WHERE salary REGEXP '1000|2000' --OR匹配

sql 复制代码

SELECT * FROM users WHERE name REGEXT '[cz]ong' -- 单一字符，会匹配cong / zong； 可以使用否定 例如：[^cz]

其他支持的正则 [0-9], \\-（特殊字符）-, 也可以用来引用元字符，例如\\f (换页)， \\n （换行），\\r （换行）, \\t （制表）, \\v （纵向制表），\\ （反斜线），多数正则表达式使用单个反斜线转义特殊字符，mysql要求使用两个，一个由mysql解析，另外一个由正则表达式库解析。

like 也支持binary来区分大小写 like binary, 大小写主要取决于表的校对规则collect

字符类（character class）

类	说明
[:alnum:]	任意字母和数字.(同[ a-zA-Z0-9])
[:alpha:]	任意字符（同[ a-zA-Z])
[:blank:]	空格和制表（同[ \lt])
[:cntrl:]	ASCII控制字符（ASCII 0到31和127)
[:digit:]	任意数字（同[0-9])
[:graph:]	与[ :print : ]相同，但不包括空格
[:lower:]	任意小写字母(同[ a-z])
[:print:]	任意可打印字符
[:punct:]	既不在[ :alnum: ]又不在[ :cntrl: ]中的任意字符
[:space:]	包括空格在内的任意空白字符（同[ \lf\ln \lrlltllv])
[:upper:]	任意大写字母(同[A-Z])
[:xdigit:]	任意十六进制数字（同[ a-fA-FO-9])

sql 复制代码

select * from contracts where amount regexp '[[:digit:]]{2,4}'
-- 匹配2到四位数字

匹配多个实例

支持*,+, ?, {n}, {n,}, {n,m} 其中m不能大于255 （mysql8测试可以输入大于255的数字，但是有没有效果没有测试）

元字符

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

匹配词的边界，兼容perl的正则表达式通常使用\b单词边界或者\B 非单词边界

使REGEXP起类似LIKE的作用， LIKE和REGEXP的不同在于、LIKE匹配整个串而REGEXP匹配子串。利用定位符,通过用^开始每个表达式，用$结束每个表达式可以使REGEXP的作用与LIKE一样。

简单的正则表达式测试可以在不使用数据库表的情况下用SELECT来测试正则表达式。 REGEXP检查总是返回0(没有匹配）或1(匹配）。可以用带文字串的REGEXP来测试表达式，并试验它们。相应的语法如下:

sql 复制代码

SELECT 'hello' REGEXP '[0-9]';

分组

group by 必须出现在where之后order by之前

使用with rollup 可以获取分组汇总级别

sql 复制代码

select count(id), id from users group by id with rollup;

我们经常发现用GROUP BY分组的数据确实是以分组顺序输出的。但情况并不总是这样，它并不是SQL规范所要求的。此外，用户也可能会要求以不同于分组的顺序排序。仅因为你以某种方式分组数据（获得特定的分组聚集值)，并不表示你需要以相同的方式排序输出。应该提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句也是如此。

不要忘记ORDER BY一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

排序

随机排序

复制代码

order by rand()

取消排序

复制代码

order by null

自定义排序

复制代码

order by field(id, 1,3,4)

根据id自定义排序，如果id是null或者不存在于后面的列表中则排序为0，否则按照后面给定的排序进行排序

SQL子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

子查询

sql 复制代码

select * from maker where country_id in (select country_id from country);

联表

自联结
内联
外联【左，右】
full join

组合查询

union 会去除重复行
union all 不会去除重复行

索引

禁用、启用索引

sql 复制代码

alter table users disable keys; --禁用
alter table users enable keys; --启用

在批量插入数据之前禁用索引，插入完成后启用索引

创建索引

添加PRIMARY KEY（主键索引）

复制代码

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

添加UNIQUE(唯一索引)

复制代码

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

唯一索引与逻辑删除可能导致冲突，简单的解决办法是和逻辑删除标识字段建立唯一索引

添加INDEX(普通索引)

复制代码

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

添加FULLTEXT(全文索引)

建表时建立

sql 复制代码

CREATE TABLE article ( 
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    title VARCHAR(200), 
    body TEXT, 
    FULLTEXT(title, body) 
) TYPE=MYISAM;

修改

sql 复制代码

ALTER TABLE `table_name` ADD FULLTEXT INDEX index_name( `column`, `column2`);

直接添加

sql 复制代码

CREATE FULLTEXT INDEX index_name(`column`) on `table_name`;

添加多列索引

sql 复制代码

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

修改索引

mysql中没有真正意义上的修改索引，只有先删除之后在创建新的索引才可以达到修改的目的，原因是mysql在创建索引时会对字段建立关系长度等，只有删除之后创建新的索引才能创建新的关系保证索引的正确性；

如：将login_name_index索引修改为单唯一索引；

复制代码

DROP INDEX login_name_index ON `user`; 
ALTER TABLE `user` ADD UNIQUE login_name_index ( `login_name` );

删除索引

格式：DROP INDEX 索引名称 ON 表名;

sql 复制代码

DROP INDEX login_name_index ON 'user';

ALTER TABLE `table_name` DROP INDEX 'index_name';

sql 复制代码

ALTER TABLE users DROP primary key; --删除前要将auto_increment去掉

查询索引

格式：SHOW INDEX FROM 表名;

复制代码

SHOW INDEX FROM `user`;

全文索引

全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的-一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配(哪些行包含它们)，哪些词不匹配，它们匹配的频率，等等。

Mysql5.7之后innodb引擎也支持全文索引，并且5.7.6之后还内置了ngram扩展

配置ngram

复制代码

[mysqld]
ngram_token_size=2

通常在建表的时候添加fulltext索引，例如

sql 复制代码

create table notes (
	title text,
	fulltext(title)
)engine=myisam;

也可以使用create index 或者alter table来添加索引

sql 复制代码

alter table articles add fulltext index ft_idx(`content`) with parser ngram;
create fulltext index on articles(`content`) with parser ngram;

全文苏检索支持自然语言模式和boolean模式

match() against() 还可以作为一个列，

复制代码

select match(title) against('rabbit') as sc from notes;

sc 表示全文检索的计算出的等级值

使用

参考：MySQL使用全文索引(fulltext index)_椰汁菠萝-CSDN博客_fulltext index

使用全文索引需要注意的是：(基本单位是词)， MySQL默认的分词是所有非字母和数字的特殊符号都是分词符

使用Match() 和 Against() 来使用全文索引进行检索

sql 复制代码

select note_text from notes where match(note_text) against('book');

match() 可以包含多个列，例如match(title,content) , 应该和建立索引时候的顺序一致

当查询多列数据时，建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的

检索的文本越靠前，等级值越大。如果包含多个搜索项，则包含多个匹配词的行的等级值更高

查询扩展

扫描步骤如下：

全文检索，筛选结果行
检查匹配行，选择有用词
再次全文检索，包含上个步骤选择词

sql 复制代码

select note_text from notes where match(note_text) against('book' with query expansion);

记录越多，文本越多，使用查询扩展返回的结果越好

布尔全文检索

即使没有fulltext索引也可以使用，但是比较慢且性能差

关键点：

要匹配的词
要排除的词（即使有匹配到的词也会排除掉）
排列提示（指定一些更重要的词，越重要的词等级越高）
表达式分组
其他

sql 复制代码

--匹配heavy 排除rope*（rope开头的词）
SELECT note_ text FROM productnotes WHERE Match(note_ text) Agai nst( 'heavy -rope*' IN BOOLEAN MODE);

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含且增加等级值
<	包含且减少等级值
（）	把词组成子表达式，允许这些组表达式作为一个组被包含，排除，排列等
~	取消一个词的排序值
*	词尾的通配符
""	定义一个短语，它匹配整个短语

sql 复制代码

SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bait'IN BOOLEAN MODE);
--这个搜索匹配包含词rabbit和bait的行。
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit bait'IN BOOLEAN MODE); 
--没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。
SELECT note_text FROM productnotes WHERE Match(note_text) Against('"rabbit bait"'IN BOOLEAN MODE); 
--这个搜索匹配短语rabbitbait而不是匹配两个词rabbit和bait。
SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit <carrot boolean mode select note_text from productnotes where match against> 扩展

- 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词(如果需要,这个数目可以更改)。
- MySQL带有一个内建的非用词(stopword) 列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表(请参阅MySQL文档以了解如何完成此工作)。
- 许多词出现的频率很高，搜索它们没有用处(返回太多的结果)。因此，MySQL规定了一条50%规则，如果-一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
- 如果表中的行数少于3行，则全文本搜索不返回结果(因为每个词或者不出现，或者至少出现在50%的行中)。
- 忽略词中的单引号。例如，don't索 引为dont。
- 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。

**邻近操作符**

# 视图

## 使用视图的优势：

2. 重用SQL语句
3. 简化复杂的SQL
4. 使用表的组成部分而不是整个表，因此可以作保保护数据即授予表特定部分的访问权限，而不是整个表
5. 更改数据格式，视图可以返回与底层表示和格式不同的数据

视图本身不包含数据，使用视图实际上是使用视图的查询规则检索数据。如果修改源表的数据，视图中查询到的数据也会被修改。

> 可以通过视图修改数据

## 视图创建的规则和限制

1. 视图名不可与其他视图或表名重复
2. 创建视图数量没有限制
3. 创建视图必须有相应访问权限
4. 视图可以嵌套（使用视图再创建视图）
5. ORDER BY可以在视图中使用，但是如果检索的SQL中有ORDER BY则视图中的被覆盖
6. 视图不能索引，也不能有关联的触发器或者默认值
7. 视图可以和表一起使用

## 使用视图

### 创建视图

```sql
create view <view_name> as <dql>

查看创建视图语句

sql 复制代码

show create view <view_name>

删除视图

sql 复制代码

drop view <view_name>

修改视图

可以先删除视图在创建，也可以

sql 复制代码

create or repace view <view_name>

视图可以更新，但是如果Mysql认为基数据不能被正确更新，则不会更新，在以下情况下不会更新

分组 group by 和having
联结
子查询
并
聚集函数
Distinct
导出（计算）列

上面的情况可能随着版本不同而有差异，一般情况下视图用来检索

存储过程

使用存储过程

sql 复制代码

call procedure_name(@in, @out)

创建存储过程

复制代码

create procedure procedure_name() comment 'procedure comment'
begin
select * from notes;
end;

注意要先使用\d $或者delimiter$将分隔符;临时更改为$或者其他字符

其中()中可接受参数，begin, end 包含的为存储过程体

可以使用参数

复制代码

create procedure get_username(in id int, out name varchar(10))
begin
select `username` into name from users where `id` = id;
end;
--没有测试，正确性未知

所有变量都必须以@开始

调用

复制代码

call get_username(1, @ret);

查询执行返回值

复制代码

select @ret;

定义变量

sql 复制代码

declare total decimal(8,2) default 0;

判断

sql 复制代码

if <variable> then
select * from users;
end if;

查看存储过程

sql 复制代码

show procedure status [like 'keywords'];
show create procedure <procudure_name>;

示例

sql 复制代码

DELIMITER //  -- 将分隔符更改为//
CREATE PROCEDURE increment_or_insert(
    IN p_keyword VARCHAR(255),
    IN p_updated_at TIMESTAMP
) BEGIN DECLARE row_id BIGINT;
-- 选择符合条件的行的id
SELECT
    id INTO row_id
FROM
    search_logs
WHERE
    `keyword` = p_keyword
LIMIT
    1;
-- 打印row_id的值
SELECT
    CONCAT('Row id is ', row_id) AS Message;
IF row_id IS NOT NULL THEN -- 如果存在符合条件的行，就增加字段的值
UPDATE
    search_logs
SET
    `frequency` = `frequency` + 1
WHERE
    `id` = row_id;
ELSE -- 如果不存在符合条件的行，就插入一行数据
INSERT INTO
    search_logs (`keyword`, `updated_at`)
VALUES
    (p_keyword, p_updated_at);
END IF;
END
DELIMITER ; -- 将分隔符更改为;

游标

MySQL游标不同于其他多数DBMS，只能用于存储过程和函数【早期资料】

使用游标

使用游标前应该先声明（定义），这个过程实际没有检索数据，只是定义要使用的SELECT 语句
一旦声明后，应该打开游标来使用，这个过程用前面定义的SELECT 语句把数据检索出来
对于填有数据的游标，根据需要取出（检索）各行
结束后必须关闭游标

声明游标后，可以频繁地打开或者关闭游标，游标打开后，可以频繁地执行取操作

创建游标

使用declare 定义游标

复制代码

declare products_f cursor for select * from products order by id;

定义游标之后可以打开游标

复制代码

open products_f;

open 语句执行查询，存储检索到地数据以提供浏览和滚动。

游标处理完成，应该关闭游标

复制代码

close products_f;

关闭游标会释放游标所使用地内存等资源，所以当游标不再需要使用地时候就应该关闭游标，如果你不明确关闭游标，MySQL会在end语句时关闭游标。

使用游标数据

游标被打开后，可以使用fetch语句访问它的每一行。fetch指定检索所需的列，数据存放位置，还会向前移动游标内部指针，使下一条fetch语句检索下一行。

复制代码

fetch products_f into o;

检索游标中结果的第一行并将数据存放到局部变量o中，再次执行fetch将检索下一行，除了使用repeat ,还可以使用其他循环语句来重复执行，直到使用leave手动退出，通常repeat的语法更适合对游标进行循环。

例子

sql 复制代码

create procedure products_p()
begin
	declare done boolean default 0;
	declare o int;
	declare products_f cursor 
	for 
	select id from users;
	-- continue handler 条件出现时被执行的代码 即 SQLSTATE '02000'出现时执行set done = 1
	-- SQLSTATE '02000'是一个未找到条件，即没有更多行时候不再循环 ，更多错误代码列可以查看MySQL手册https://dev.mysql.com/doc/refman/8.0/en/error-handling.html
	declare continue handler for SQLSTATE '02000' set done = 1;
	open products_f;
	-- 反复执行知道done为真
	repeat
		fetch products_f into o;
	until done end repeat;
	close products_f;
end

DECLARE语句的次序： DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义，不遵守此顺序将产生错误消息。

事务

Mysql不支持事务嵌套，会隐式提交事务 https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

术语

**事务（transaction）**指一组SQL语句

**回滚（rollback）**撤销指定SQL执行过程

**提交（commit）**将为存储的SQL语句结果写入数据表

**保留点（savepoint）**事务处理中设置的临时占位符（place holder） ,可以发布或者回退（与回退整个事务不同）

事务用来管理insert, update, delete 语句，不能回退drop，select， create 操作，即使使用了也不会撤销。

隐含事务提交：commit 或者rollback 后事务会自动关闭。

保留点

简单的commit或者rollback会撤销整个事务，但是实际上可能需要回滚或者提交一部分事务，就需要在事务处理块中放置合适的保留点，回滚时回滚到某个保留点，可以使用savepoint创建保留点。

复制代码

savepoint delete1;

当需要回滚时，可以指定保留点

复制代码

rollback to [savepoint] delete1

可以尽可能多地创建保留点，因为这样就可以更灵活地控制事务。当执行commit 或者rollback 后自动释放保留点，也可以使用release savepoint 释放保留点。

更改默认提交行为

关闭自动提交

复制代码

set autocommit = 0

该标志是针对当前连接而不是服务器

字符集和编码

字符集 字母和符号的集合

编码某个字符集内成员的内部表示

校对规定字符集如何比较的指令

查看可用的校对和字符集

复制代码

show collation;
show variables like '%character%';
show variables like '%collation%';

可以给单个表，单个列设置字符集和校对

select 语句中可以指定使用不同的校对，例如可以使用区分大小写的校对

复制代码

select * from users order by username collate latin1_general_cs;

使用cast() 或者convert() 可以在字符集之间转换

数据维护

analyze table 检查表键是否正确
check table 针对许多问题进行检查，在myisam引擎上还针对索引进行检查 , 如果在Myisam引擎上产生的结果不正确，可能需要使用repare table 修复响应的表。
changed 检查自最后一次检查以来改动过的表
extended 执行最彻底的检查
fast 检查未正常关闭的表
medium 检查所有被删除的链接并进行键检验
quick 只进行快速扫描
optimize table 优化表，如果有大量删除操作，应该使用

诊断启动问题

--safe-mode 装载减去某些最佳配置的服务器
--verbose 显示全文本消息，常与--help 联合使用

查看日志文件

错误日志，包含启停或任意关键位置的错误细节，日志名通常为hostname.err 位于data 目录，可用--log-error 命令更改
查询日志，记录MySQL活动，在诊断问题时非常有用，日志可能会很快变得很大，所以不应该长期使用，位于data 目录，可用--log更改
二进制日志(bin-log)，记录更新或者可能更新过数据的所有语句，日志名通常为hostname-bin，位于data 目录，可用--log-bin 更改
慢查询日志，可用--log-slow-queries 更改

其他语法

alter

复制代码

alter table <table_name>
(
	add column <column_name>    datatype [null|not null] [constraint],
	change column <column_name> datatype [null|not null] [constraint], 
	drop column <column_name>
	add index <column_name> 
)

ALTER table Teacher change Tid Tnum int; //修改列名

修改表结构添加字段还可以使用after和first来指定字段位置

create index

复制代码

create index <index_name> on <table_name> (column [desc|asc], ...);

create user

复制代码

create user <username>[@host] identified [with mysql_native_password] by <password>;

drop

复制代码

drop database|index|procedure|table|trigger|user|view|itemname;

insert select

复制代码

insert into users select * from users_bak;

select

sql 复制代码

select 
	column 
from 
	table_name
where
union
group by 
having
order by

事务

复制代码

start transaction;

排序

字符串以字典顺序排序，从左向右一次比较每一个字符，这将导致‘10‘位于’2‘之前，数值才能正确排序。

删除

复制代码

drop procedure <procedure_name> [if exists]

插入

sql 复制代码

insert 语句耗时，可能因为等待而降低select性能，所以使用下面的语句降低有限级
INSERT LOW_PRIORITY INTO
insert ignore -- 如果冲突，则不插入
insert into users (id, name) values (1, '张三') on duplicate key update name = '李四'; -- 当冲突时将名字更新为李四，如果行作为新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2
replace into -- 如果冲突则删除再插入
--降低insert的有限级，同样适用于update,delete。

深入mysql “ON DUPLICATE KEY UPDATE” 语法的分析 | Specs' Blog-就爱PHP (9iphp.com)

MySQL "replace into" 的坑 (xupeng.me)

更新

更新可以使用子查询，即将查到的值更新到列。
update ignore 可以忽略错误

更新表不能包含该表的子查询例如

复制代码

update user set sex = 1 where id in (select id from user where age > 8)

上面的SQL会报错，应该更改为

复制代码

update user left inner join (select id from user where age > 8) set sex = 1;

上面的SQL只是示例

删除

复制代码

truncate [table] users;
delete from

其他

外键不能跨引擎，例如myisam不能添加innodb表的主键为外键

sql 复制代码

rename table notes to note;
alter table notes rename to note;

Mysql添加自增列

两句查完：

sql 复制代码

set @rownum=0;

select (@rownum:=@rownum+1),colname from [tablename or (subquery) a];

一句查完：

复制代码

select @rownum:=@rownum+1,colnum from (select @rownum:=0) a,[tablename or (subquery) b];

多条SQL要用;分割，单条SQL末尾的分号非必须，推荐加上分号，如果使用命令行，则必须要有结尾符。

SQL 语句不区分大小写。最佳的方式是按照大小写惯例，且使用时保持一致。

SQL可以分成多行，用更直观的格式表达

sql 复制代码

desc / describe <table>;

其他

shell 复制代码

tee /home/cheng/log.sql

将下面的操作都写入改日志

sql 复制代码

grant all privileges on testdb.* to ‘test_user’@’localhost’ identified by “jack” with grant option;

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。
如果不想这个用户有这个grant的权限，可以不加这句

mysql的SQL长度限制

shell 复制代码

max_allowed_packet = 6M

把上面这个配置改大就行了

记录执行sql日志

登录mysql

复制代码

set global general_log_file='/tmp/general.log';
set global general_log='on';

之后执行命令都会记录进LOG

排序

MySQL中的排序ORDER BY 除了可以用ASC和DESC，还可以自定义字符串/数字来实现排序。

格式如下：

复制代码

SELECT * FROM table ORDER BY FIELD(status,1,2,0);

这样子写的话，返回的结果集是按照字段status的1、2、0进行排序的，当然，也可以对字符串进行排序。

原理如下：

FIELD()函数是将参数1的字段对后续参数进行比较，并返回1、2、3等等，如果遇到null或者没有在结果集上存在的数据，则返回0，然后根据升序进行排序。

生成列

MySQL 5.7引入了一个名为生成列的新功能。它之所以叫作生成列，因为此列中的数据是基于预定义的表达式或从其他列计算的。

例如，假设有以下结构的一个contacts表：

sql 复制代码

CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

要获取联系人的全名，请使用CONCAT()函数，如下所示：

sql 复制代码

SELECT 
    id, CONCAT(first_name, ' ', last_name), email
FROM
    contacts;

这不是最优的查询。

通过使用MySQL生成的列，可以重新创建contacts表，如下所示：

sql 复制代码

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)),
    email VARCHAR(100) NOT NULL
);

GENERATED ALWAYS as(expression)是创建生成列的语法。要测试“全名”列，请在contacts表中插入一行。

sql 复制代码

INSERT INTO contacts(first_name,last_name, email)
VALUES('john','doe','john.doe@yiibai.com');

现在，可以从contacts表中查询数据。

当从contacts表中查询数据时，fullname列中的值将立即计算。

MySQL提供了两种类型的生成列：存储和虚拟。每次读取数据时，虚拟列都将在运行中计算，而存储的列在数据更新时被物理计算和存储。

基于此定义，上述示例中的fullname列是虚拟列。

MySQL生成列的语法

定义生成列的语法如下：

sql 复制代码

column_name data_type [GENERATED ALWAYS] AS (expression)
   [VIRTUAL | STORED] [UNIQUE [KEY]]

首先，指定列名及其数据类型。

接下来，添加GENERATED ALWAYS子句以指示列是生成的列。

然后，通过使用相应的选项来指示生成列的类型：VIRTUAL或STORED。默认情况下，如果未明确指定生成列的类型，MySQL将使用VIRTUAL。

之后，在AS关键字后面的大括号内指定表达式。该表达式可以包含文字，内置函数，无参数，操作符或对同一表中任何列的引用。如果你使用一个函数，它必须是标量和确定性的。

最后，如果生成的列被存储，可以为它定义一个唯一约束。

MySQL存储列示例

我们来看一下products表。

sql 复制代码

mysql> desc products;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| productCode        | varchar(15)   | NO   | PRI |         |       |
| productName        | varchar(70)   | NO   |     | NULL    |       |
| productLine        | varchar(50)   | NO   | MUL | NULL    |       |
| productScale       | varchar(10)   | NO   |     | NULL    |       |
| productVendor      | varchar(50)   | NO   |     | NULL    |       |
| productDescription | text          | NO   |     | NULL    |       |
| quantityInStock    | smallint(6)   | NO   |     | NULL    |       |
| buyPrice           | decimal(10,2) | NO   |     | NULL    |       |
| MSRP               | decimal(10,2) | NO   |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
9 rows in set

使用quantityInStock和buyPrice列的数据，通过以下表达式计算每个SKU的股票值：

sql 复制代码

quantityInStock * buyPrice

但是，可以使用以下ALTER TABLE … ADD COLUMN语句将名为stock_value的存储的生成列添加到products表：

sql 复制代码

ALTER TABLE products
ADD COLUMN stockValue DOUBLE 
GENERATED ALWAYS AS (buyprice*quantityinstock) STORED;

通常，ALTER TABLE语句需要完整的表重建，因此，如果更改大表是耗时的。但是，虚拟列并非如此。

现在，我们可以直接从products表中查询库存值。

sql 复制代码

SELECT 
    productName, ROUND(stockValue, 2) AS stock_value
FROM
    products;

执行上面查询语句，得到以下结果 -

shell 复制代码

+---------------------------------------------+-------------+
| productName                                 | stock_value |
+---------------------------------------------+-------------+
| 1969 Harley Davidson Ultimate Chopper       |   387209.73 |
| 1952 Alpine Renault 1300                    |   720126.90 |
| 1996 Moto Guzzi 1100i                       |   457058.75 |
| 2003 Harley-Davidson Eagle Drag Bike        |   508073.64 |
| 1972 Alfa Romeo GTA                         |   278631.36 |
| 1962 LanciaA Delta 16V                      |   702325.22 |
| 1968 Ford Mustang                           |     6483.12 |
|************** 省略了一大波数据 ****************************|
| The Queen Mary                              |   272869.44 |
| American Airlines: MD-11S                   |   319901.40 |
| Boeing X-32A JSF                            |   159163.89 |
| Pont Yacht                                  |    13786.20 |
+---------------------------------------------+-------------+
110 rows in set

参考： MySQL生成列 - MySQL教程 (yiibai.com)

修改validate_password_policy参数的值

复制代码

set global validate_password_policy=0;
#validate_password_length(密码长度)参数默认为8，修改为需要长度
set global validate_password_length=1;

mysqld命令

https://www.cnblogs.com/shymen/p/8850655.html

utf8_unicode_ci与utf8_general_ci的区别

当前，utf8_unicode_ci校对规则仅部分支持Unicode校对规则算法。一些字符还是不能支持。并且，不能完全支持组合的记号。这主要影响越南和俄罗斯的一些少数民族语言，如：Udmurt 、Tatar、Bashkir和Mari。

utf8_unicode_ci的最主要的特色是支持扩展，即当把一个字母看作与其它字母组合相等时。例如，在德语和一些其它语言中‘ß’等于‘ss’。

utf8_general_ci是一个遗留的校对规则，不支持扩展。它仅能够在字符之间进行逐个比较。这意味着utf8_general_ci校对规则进行的比较速度很快，但是与使用utf8_unicode_ci的校对规则相比，比较正确性较差）。

例如，使用utf8_general_ci和utf8_unicode_ci两种校对规则下面的比较相等：

Ä = A

Ö = O

Ü = U

两种校对规则之间的区别是，对于utf8_general_ci下面的等式成立：

ß = s

但是，对于utf8_unicode_ci下面等式成立：

ß = ss

对于一种语言仅当使用utf8_unicode_ci排序做的不好时，才执行与具体语言相关的utf8字符集校对规则。例如，对于德语和法语，utf8_unicode_ci工作的很好，因此不再需要为这两种语言创建特殊的utf8校对规则。

utf8_general_ci也适用与德语和法语，除了‘ß’等于‘s’，而不是‘ss’之外。如果你的应用能够接受这些，那么应该使用utf8_general_ci，因为它速度快。否则，使用utf8_unicode_ci，因为它比较准确。

其他

修改表引擎

sql 复制代码

ALTER TABLE users ENGINE = myisam;

执行计划中extra的描述

Using where：表示优化器需要通过索引回表查询数据；
Using index：表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；
Using index condition：在5.6版本后加入的新特性（Index Condition Pushdown）;
Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；
Using where && Using index

数据类型相关

记录datetime到毫秒
5.6 之后可以将字段类型设置为datetime(3), 其中的数字表示精度，三位精确到毫秒

null对order by的影响

1.oracle 结论 (null 最大)

order by colum asc 时，null默认被放在最后
order by colum desc 时，null默认被放在最前
nulls first 时，强制null放在最前，不为null的按声明顺序[asc|desc]进行排序
nulls last 时，强制null放在最后，不为null的按声明顺序[asc|desc]进行排序

2.mysql,sql server 结论 (null 最小)
order by colum asc 时，null默认被放在最前
order by colum desc 时，null默认被放在最后
ORDER BY IF(ISNULL(update_date),0,1) null被强制放在最前，不为null的按声明顺序[asc|desc]进行排序
ORDER BY IF(ISNULL(update_date),1,0) null被强制放在最后，不为null的按声明顺序[asc|desc]进行排序

复制代码

SELECT * FROM t1 where 1=1 ORDER BY IF(ISNULL(order_index),1,0),order_index asc,create_time desc

mysql nulls first nulls last解决方案
nulls first:

order by IF(ISNULL(my_field),0,1),my_field;
nulls last:

order by IF(ISNULL(my_field),1,0),my_field;
ISNULL函数当my_field字段为空是，返回1，当不为空时返回0

IF函数，如果第一个表达式为真，则返回第二个参数的值，否则，返回第三个参数的值。

EXTRACT(unit FROM date)

pgsql null排在有值的行前面还是后面通过语法来指定

复制代码

--null值在前
select * from tablename order by id nulls first;
--null值在后
select * from tablename order by id nulls last;
--null在前配合desc使用
select * from tablename order by id desc nulls first;
--null在后配合desc使用
select * from tablename order by id desc nulls last;
举例:
null值在后,先按照count1降序排列,count1相同再按照count2降序排列
order by count1 desc nulls last, count2  desc nulls last;

mysql的null值排序和pgsql相反
https://www.w3school.com.cn/sql/func_extract.asp

查看数据库大小，容量

查询所有数据库的总大小，方法如下：

sql 复制代码

mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;
+-----------+
| data      |
+-----------+
| 3052.76MB |
+-----------+
1 row in set (0.02 sec)

统计一下所有库数据量

每张表数据量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH

sql 复制代码

SELECT SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb FROM information_schema.TABLES

统计每个库大小：

复制代码

SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb FROM information_schema.TABLES group by table_schema;

查看指定数据库的大小，比如说：数据库test，方法如下：

sql 复制代码

mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='test';
+----------+
| data     |
+----------+
| 142.84MB |
+----------+
1 row in set (0.00 sec)

查看所有数据库各容量大小

复制代码

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

查看所有数据库各表容量大小

复制代码

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

查看指定数据库容量大小

例：查看mysql库容量大小

复制代码

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

查看指定数据库各表容量大小

例：查看mysql库各表容量大小

sql 复制代码

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

mysql查看执行sql语句的记录日志

1、使用processlist，但是有个弊端，就是只能查看正在执行的sql语句，对应历史记录，查看不到。好处是不用设置，不会保存。

sql 复制代码

use information_schema;
show processlist;

或者：

sql 复制代码

select * from information_schema.`PROCESSLIST` where info is not null;

2、开启日志模式

1、设置

sql 复制代码

SET GLOBAL log_output = 'FILE';SET GLOBAL general_log = 'ON';  --日志开启
SET GLOBAL log_output = 'FILE'; SET GLOBAL general_log = 'OFF';  --日志关闭

这两个变量也可以直接写在配置中，log_output可以是TABLE/FILE，默认是FILE

2、查询

sql 复制代码

SELECT * from mysql.general_log ORDER BY event_time DESC;

3、清空表（delete对于这个表，不允许使用，只能用truncate）

sql 复制代码

truncate table mysql.general_log;

在查询sql语句之后，在对应的 data 文件夹下面有对应的log记录。在查询到所需要的记录之后，应尽快关闭日志模式，占用磁盘空间比较大

sql 复制代码

-- 查看日志是否开启
SHOW VARIABLES LIKE 'general_log'; 
-- 开启日志功能
SET GLOBAL general_log='ON';
-- 关闭日志功能
SET GLOBAL general_log='OFF'; 
-- 看看日志文件保存位置
SHOW VARIABLES LIKE 'general_log_file'; 
-- 设置日志文件保存位置 C:\ProgramData\MySQL\MySQL Server 5.5\Data\DESKTOP-NUR8UC7.log
SET GLOBAL general_log_file='C:\\tmp.log'; 
 -- 看看日志输出类型 TABLE 或 FILE
SHOW VARIABLES LIKE 'log_output';
 -- 设置输出类型为 TABLE
SET GLOBAL log_output='TABLE';
 -- 设置输出类型为FILE
SET GLOBAL log_output='FILE';

提取mysql慢sql的shell脚本，适合crontab使用

shell 复制代码

#!/bin/bash

Version="0.1"

init()
{
    #环境变量
    source /etc/profile
    export PATH=${PATH}:/usr/sbin
    #脚本所在目录
    SHELL_FOLDER=$(dirname $(readlink -f "$0"))
    #慢sql文件
    slowLogFile="/hkdata/mysql/slow.log"
    #是否告警
    IS_alert="1"
    #邮箱地址
    Mail=""
    #本机IP地址
    Ip_Addr="`ip a s | grep global | grep -v "192.168" | awk '{print $2}' | head -n 1 | cut -d "/" -f 1`"
    #慢SQL秒，大于等于
    sec="5"
}

main()
{
    #判断日志文件是否存在
    if ! test -f $slowLogFile; then
        echo "$slowLogFile 日志文件不存在！"
        exit 0
    fi
    #获取文本内部开始时间（第一次运行时默认从上次记录文本里取，如果没有默认查找slow最后行的）
    if test -f startDATE.txt; then
        startDATE="$(cat ${SHELL_FOLDER}/startDATE.txt)"        #开始时间（当分隔符），如果不存在也没事
    else
        :
    fi
    endDATE="$(tail -n 1024 ${slowLogFile} | grep "# Time:" | tail -n1 | cut -d "+" -f 1)"      #结束时间（当分隔符）
    if test "$endDATE" = ""; then                                                               #如果为空增大查找范围
        endDATE="$(tail -n 4096 ${slowLogFile} | grep "# Time:" | tail -n1 | cut -d "+" -f 1)"
    fi
    if test "$endDATE" != ""; then                                                              #下一次运行充当开始时间的分隔符
        echo $endDATE > startDATE.txt
    fi
    #startDATE="Time: 2022-08-30T10:22:35.455603"       #测试
    #endDATE="# Time: 2022-08-30T10:26:15.097185"       #测试
    if test "$startDATE" != "$endDATE"; then
        awk '/'"${startDATE}"'/,/'"${endDATE}"'/{if(i>=1)print x; x=$0; i++}' ${slowLogFile} > ${SHELL_FOLDER}/newSlow.txt      #正则匹配，输出 "# Time: 2022-08-19T16:54:45.849179"行 至 "# Time: 2022-08-19T16:54:45.849179"行 到newSlow.txt文件
        echo $endDATE >> ${SHELL_FOLDER}/newSlow.txt
        cat ${SHELL_FOLDER}/newSlow.txt | while read line; do       #判断输出文件的否合格（可与忽略）
            result=$(echo $line | grep "# Time: ")      
            if [[ "$result" != "" ]]; then
                echo $line >> $SHELL_FOLDER/temp.txt        
            else
                echo $line >> $SHELL_FOLDER/temp.txt
            fi
        done
    else
        echo "文本没有变化！没有新日志产生"
        exit 0                      #开始时间等于结束时间时，slow.log文件没有更新。退出
    fi
}

Split_text()
{
    TArray=(`awk '/Time:/,/Time:/{print}' $SHELL_FOLDER/temp.txt  | cut -d "+" -f 1 | awk '{print $3}'`)             #从$SHELL_FOLDER/temp.txt 文件中找"时间"，并存储在数组中当分割符。（目的是分割每一个慢sql）
    echo ${TArray[0]}
    for((i=0; i<${#TArray[@]}; i++))            #遍历数组
    do
        echo "$i"
        let t=i                             #i作为数组下标
        let t=t+1                           #t作为下一个数组下标
        awk '/'"${TArray[i]}"'/,/'"${TArray[t]}"'/{if(i>=1)print x; x=$0; i++}' ${slowLogFile} > ${SHELL_FOLDER}/3.txt   #分割好的每个sql信息存储在3.txt
        Time="`grep Query_time 3.txt | awk '{print $3}' | cut -d "." -f 1`"                                      #查找3.txt的Query_time值
        echo "慢SQL时间: ${Time}"
        if test "${Time}" != ""; then
            if [ "${Time}" -ge "${sec}" ]; then     #大于等于5秒的sql
                cat 3.txt >> sql5.log               #追加到5秒集合中
            fi
        fi
    done
    #删除不需要的文件
    rm ${SHELL_FOLDER}/temp.txt
    rm ${SHELL_FOLDER}/newSlow.txt
}

send_mail()
{
    if test "$IS_alert" = "1"; then     #是否告警
        back_size5=$(stat -c "%s" $SHELL_FOLDER/sql5.log)
        if [[ $back_size5 > 1 ]];then
            echo "出现慢sql(超过${sec}s)，附件为sql语句。服务器IP:${Ip_Addr}" | \
                mail -s "慢sql语句(超过${sec}s),服务器IP:${Ip_Addr}" -a ${SHELL_FOLDER}/sql5.log ${Mail}
            echo "" > ${SHELL_FOLDER}/sql5.log              #发送完邮件后清空，等下次运行追加
        fi
    fi
}

help_()
{
    cat <<eof mysql slow version: usage: options: print debug . sql file mailing list seconds alarm or not help eof exit init while getopts :xf:m:s:a:h arg do case in x f slowlogfile="$OPTARG" m mail="$OPTARG" s sec="$OPTARG" a is_alert="$OPTARG" h help_ esac done shift test set main split_text send_mail varchar>
        

        
    


<script first>
    var ele = window.Element;
    Dcat.eMatches = ele.prototype.matches ||
        ele.prototype.msMatchesSelector ||
        ele.prototype.webkitMatchesSelector;
</script>

<script require="@editor-md-form" init="#form-USlWTQhc .field_content._normal_">
    editormd(id, {"height":500,"codeFold":true,"saveHTMLToTextarea":true,"searchReplace":true,"emoji":true,"taskList":true,"tocm":true,"tex":true,"flowChart":false,"sequenceDiagram":false,"imageUpload":true,"autoFocus":true,"path":"https:\/\/www.codeemo.cn\/vendor\/dcat-admin\/dcat\/plugins\/editor-md\/lib\/","name":"content","placeholder":"Input content","readonly":false,"imageUploadURL":"https:\/\/www.codeemo.cn\/admin\/dcat-api\/editor-md\/upload?_token=s0GxANGOa2EQmRCZmBBpqbuoAH1DqM3qS8UTHG7v&dir=markdown%2Fimages"});

    Element.prototype.matches = Dcat.eMatches;
</script>
</eof></table></procedure_name></password></username></table_name></index_name></column_name></column_name></column_name></column_name></table_name></procudure_name></variable></view_name></view_name></view_name></dql></view_name></carrot></table></column></name></table>

对这篇文章有意见或建议？提交反馈