一切福田，不離方寸，從心而覓，感無不通。

Category Archives: Database

如何备份PostgreSQL数据库 常见的几个备份命令使用

一般我们建站使用较多的还是固定开源CMS程序，且基本上也使用的是PHP+MYSQL程序，所以数据库上较多的还是使用的MYSQL数据库。但是前几天老左有遇到一个网友他使用的是PostgreSQL数据库，实际上这个数据库我们在使用虚拟主机的时候也是有见过的，但是确实用的不多。 如果我们需要备份PostgreSQL数据库的话，一般如果我们服务器WEB环境自带的管理小工具是可以直接导出备份的，但是如果是运维工程师自己编译的WEB系统，那我们就需要用到其他的单独命令备份，这里我们记录下如何通过PostgreSQL命令备份数据库。 第一、一键快速备份单数据库 su – postgres 这里我们登陆数据库。 pg_dump laozuo.org > laozuo.org.bak 通过命令一键将我们的数据库名换成我们需要备份的，然后备份。这里我们可以将备份的数据下载到本地。 psql laozuo < dbname.bak 如果我们需要恢复数据库可以用psql命令来恢复，是不是有点像我们MYSQL恢复数据一样。 第二、远程备份数据库 一般远程备份数据库我们个人使用的不多的，我们还是老老实实在当前服务器操作比较多见，但是这里的方法老左也记录一下。 pg_dump -h 1.1.1.1 -p 1234 dbname > dbname.bak 根据命令及端口进行备份，注意数据库名修改成我们自己的。 第三、设置定时自动备份 这个我们很多朋友都有用的，一般项目文件动的不多，一般都是数据库在增减。所以我们定期备份数据库即可，但是我们需要做到的是定时备份。 1、登录数据库 su – postgres 和上面一样，我们要登录数据库，然后设置定时任务。 2、创建备份目录 mkdir -p ~/dbbackups 我们需要创建一个备份目录。 3、创建定时任务 crontab -e 然后需要编辑文件。 0 0 * * 0 pg_dump -U postgres laozuo> ~/dbbackups/laozuo.org.bak 编辑完毕保存后，我们运营一次看看，是否备份进入文件夹。 这里我们在备份完毕PostgreSQL数据库之后，我们还是需要定期下载或者SCP推送到其他服务器。之前有个朋友确实会定时备份，但是他备份到的还是自己当前服务器。于是前几天发现服务器故障数据丢失，他悲剧了。所以我们还是需要备份到本地。   from:https://www.laozuo.org/16772.html

龙生   19 Mar 2021
View Details

MySQL 查询优化

索引 索引是什么 MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。 可以简单理解为排好序的快速查找数据结构。 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。 优缺点 优点 提高数据检索的效率，降低数据库的 IO 成本。 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。 缺点 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为 更新所带来的键值变化后的索引信息。 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间 的。 B-Tree 和 B+Tree 区别 B-Tree 的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+Tree 的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。 在 B-Tree 中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+Tree 中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看 B-Tree 的性能好像要比 B+Tree 好，而在实际应用中却是 B+Tree 的性能要好些。因为 B+Tree 的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B-Tree 多，树高比 B-Tree 小，这样带来的好处是减少磁盘访问次数。尽管 B+Tree 找到一个记录所需的比较次数要比 B-Tree 多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B+Tree 的性能可能还会好些，而且 B+Tree 的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用 B+Tree 的缘故。 为什么 B+Tree 比 B-Tree 更适合实际应用中操作系统的文件索引和数据库索引？ B+Tree 的磁盘读写代价更低 B+Tree 的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B-Tree 更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。 B+Tree 的查询效率更加稳定 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。 索引分类 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引 建表时，加上 key(列名) 指定 单独创建，create index 索引名 on 表名(列名) 单独创建，alter table […]

龙生   25 Jan 2021
View Details

5分钟学会MySQL-this is incompatible with sql_mode=only_full_group_by错误解决方案

 5分钟学会MySQL-  "this is incompatible with sql_mode=only_full_group_by"错误解决方案   前言： 一、原理层面 这个错误发生在mysql 5.7 版本及以上版本会出现的问题： mysql 5.7版本默认的sql配置是:sql_mode="ONLY_FULL_GROUP_BY"，这个配置严格执行了"SQL92标准"。 很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。   二、sql层面 在sql执行时，出现该原因： 简单来说就是：输出的结果是叫target list，就是select后面跟着的字段，还有一个地方group by column，就是 group by后面跟着的字段。由于开启了ONLY_FULL_GROUP_BY的设置，所以如果一个字段没有在target list 和group by字段中同时出现，或者是聚合函数的值的话，那么这条sql查询是被mysql认为非法的，会报错误。       一、查看sql_mode的语句如下  

1
select @@GLOBAL.sql_mode;

      二、解决方案-(推荐解决方案二)   ①解决方案一：sql语句暂时性修改sql_mode

1
set @@GLOBAL.sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  问题： 重启mysql数据库服务之后，ONLY_FULL_GROUP_BY还会出现。 ②解决方案二：完美解决方案。 需修改mysql配置文件，通过手动添加sql_mode的方式强制指定不需要ONLY_FULL_GROUP_BY属性， my.cnf位于etc文件夹下，vim下光标移到最后，添加如下：

1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启mysql服务，顺利解决。   from:https://blog.csdn.net/qq_42175986/article/details/82384160

龙生   15 Jan 2021
View Details

MySQL的JOIN（一）：用法

JOIN的含义就如英文单词“join”一样，连接两张表，大致分为内连接，外连接，右连接，左连接，自然连接。这里描述先甩出一张用烂了的图，然后插入测试数据。  

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
    CREATE TABLE t_blog(
        id INT PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR(50),
        typeId INT
    );
    SELECT * FROM t_blog;
    +----+-------+--------+
    | id | title | typeId |
    +----+-------+--------+
    |  1 | aaa   |      1 |
    |  2 | bbb   |      2 |
    |  3 | ccc   |      3 |
    |  4 | ddd   |      4 |
    |  5 | eee   |      4 |
    |  6 | fff   |      3 |
    |  7 | ggg   |      2 |
    |  8 | hhh   |   NULL |
    |  9 | iii   |   NULL |
    | 10 | jjj   |   NULL |
    +----+-------+--------+
    -- 博客的类别
    CREATE TABLE t_type(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20)
    );
    SELECT * FROM t_type;
    +----+------------+
    | id | name       |
    +----+------------+
    |  1 | C++        |
    |  2 | C          |
    |  3 | Java       |
    |  4 | C#         |
    |  5 | Javascript |
    +----+------------+

笛卡尔积：CROSS JOIN 要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。下面的例子，t_blog有10条记录，t_type有5条记录，所有他们俩的笛卡尔积有50条记录。有五种产生笛卡尔积的方式如下。

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
55
56
57
58
59
    SELECT * FROM t_blog CROSS JOIN t_type;
    SELECT * FROM t_blog INNER JOIN t_type;
    SELECT * FROM t_blog,t_type;
    SELECT * FROM t_blog NATURE JOIN t_type;
    select * from t_blog NATURA join t_type;
    +----+-------+--------+----+------------+
    | id | title | typeId | id | name       |
    +----+-------+--------+----+------------+
    |  1 | aaa   |      1 |  1 | C++        |
    |  1 | aaa   |      1 |  2 | C          |
    |  1 | aaa   |      1 |  3 | Java       |
    |  1 | aaa   |      1 |  4 | C#         |
    |  1 | aaa   |      1 |  5 | Javascript |
    |  2 | bbb   |      2 |  1 | C++        |
    |  2 | bbb   |      2 |  2 | C          |
    |  2 | bbb   |      2 |  3 | Java       |
    |  2 | bbb   |      2 |  4 | C#         |
    |  2 | bbb   |      2 |  5 | Javascript |
    |  3 | ccc   |      3 |  1 | C++        |
    |  3 | ccc   |      3 |  2 | C          |
    |  3 | ccc   |      3 |  3 | Java       |
    |  3 | ccc   |      3 |  4 | C#         |
    |  3 | ccc   |      3 |  5 | Javascript |
    |  4 | ddd   |      4 |  1 | C++        |
    |  4 | ddd   |      4 |  2 | C          |
    |  4 | ddd   |      4 |  3 | Java       |
    |  4 | ddd   |      4 |  4 | C#         |
    |  4 | ddd   |      4 |  5 | Javascript |
    |  5 | eee   |      4 |  1 | C++        |
    |  5 | eee   |      4 |  2 | C          |
    |  5 | eee   |      4 |  3 | Java       |
    |  5 | eee   |      4 |  4 | C#         |
    |  5 | eee   |      4 |  5 | Javascript |
    |  6 | fff   |      3 |  1 | C++        |
    |  6 | fff   |      3 |  2 | C          |
    |  6 | fff   |      3 |  3 | Java       |
    |  6 | fff   |      3 |  4 | C#         |
    |  6 | fff   |      3 |  5 | Javascript |
    |  7 | ggg   |      2 |  1 | C++        |
    |  7 | ggg   |      2 |  2 | C          |
    |  7 | ggg   |      2 |  3 | Java       |
    |  7 | ggg   |      2 |  4 | C#         |
    |  7 | ggg   |      2 |  5 | Javascript |
    |  8 | hhh   |   NULL |  1 | C++        |
    |  8 | hhh   |   NULL |  2 | C          |
    |  8 | hhh   |   NULL |  3 | Java       |
    |  8 | hhh   |   NULL |  4 | C#         |
    |  8 | hhh   |   NULL |  5 | Javascript |
    |  9 | iii   |   NULL |  1 | C++        |
    |  9 | iii   |   NULL |  2 | C          |
    |  9 | iii   |   NULL |  3 | Java       |
    |  9 | iii   |   NULL |  4 | C#         |
    |  9 | iii   |   NULL |  5 | Javascript |
    | 10 | jjj   |   NULL |  1 | C++        |
    | 10 | jjj   |   NULL |  2 | C          |
    | 10 | jjj   |   NULL |  3 | Java       |
    | 10 | jjj   |   NULL |  4 | C#         |
    | 10 | jjj   |   NULL |  5 | Javascript |
    +----+-------+--------+----+------------+

内连接：INNER JOIN 内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集，从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN，WHERE（等值连接），STRAIGHT_JOIN,JOIN(省略INNER)四种写法。至于哪种好我会在MySQL的JOIN（二）：优化讲述。示例如下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
    SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id;
    SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;
    SELECT * FROM t_blog STRAIGHT_JOIN t_type ON t_blog.typeId=t_type.id; --注意STRIGHT_JOIN有个下划线
    SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id;
    +----+-------+--------+----+------+
    | id | title | typeId | id | name |
    +----+-------+--------+----+------+
    |  1 | aaa   |      1 |  1 | C++  |
    |  2 | bbb   |      2 |  2 | C    |
    |  7 | ggg   |      2 |  2 | C    |
    |  3 | ccc   |      3 |  3 | Java |
    |  6 | fff   |      3 |  3 | Java |
    |  4 | ddd   |      4 |  4 | C#   |
    |  5 | eee   |      4 |  4 | C#   |
    +----+-------+--------+----+------+

  左连接：LEFT JOIN 左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录（见最后三条）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
    SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
    +----+-------+--------+------+------+
    | id | title | typeId | id   | name |
    +----+-------+--------+------+------+
    |  1 | aaa   |      1 |    1 | C++  |
    |  2 | bbb   |      2 |    2 | C    |
    |  7 | ggg   |      2 |    2 | C    |
    |  3 | ccc   |      3 |    3 | Java |
    |  6 | fff   |      3 |    3 | Java |
    |  4 | ddd   |      4 |    4 | C#   |
    |  5 | eee   |      4 |    4 | C#   |
    |  8 | hhh   |   NULL | NULL | NULL |
    |  9 | iii   |   NULL | NULL | NULL |
    | 10 | jjj   |   NULL | NULL | NULL |
    +----+-------+--------+------+------+

  右连接：RIGHT JOIN 同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上右表中剩余的记录（见最后一条）。

1
2
3
4
5
6
7
8
9
10
11
12
13
    SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
    +------+-------+--------+----+------------+
    | id   | title | typeId | id | name       |
    +------+-------+--------+----+------------+
    |    1 | aaa   |      1 |  1 | C++        |
    |    2 | bbb   |      2 |  2 | C          |
    |    3 | ccc   |      3 |  3 | Java       |
    |    4 | ddd   |      4 |  4 | C#         |
    |    5 | eee   |      4 |  4 | C#         |
    |    6 | fff   |      3 |  3 | Java       |
    |    7 | ggg   |      2 |  2 | C          |
    | NULL | NULL  |   NULL |  5 | Javascript |
    +------+-------+--------+----+------------+

外连接：OUTER JOIN 外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN，但是我们可以对左连接和右连接的结果做UNION操作来实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
    SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id
    UNION
    SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
    +------+-------+--------+------+------------+
    | id   | title | typeId | id   | name       |
    +------+-------+--------+------+------------+
    |    1 | aaa   |      1 |    1 | C++        |
    |    2 | bbb   |      2 |    2 | C          |
    |    7 | ggg   |      2 |    2 | C          |
    |    3 | ccc   |      3 |    3 | Java       |
    |    6 | fff   |      3 |    3 | Java       |
    |    4 | ddd   |      4 |    4 | C#         |
    |    5 | eee   |      4 |    4 | C#         |
    |    8 | hhh   |   NULL | NULL | NULL       |
    |    9 | iii   |   NULL | NULL | NULL       |
    |   10 | jjj   |   NULL | NULL | NULL       |
    | NULL | NULL  |   NULL |    5 | Javascript |
    +------+-------+--------+------+------------+

USING子句 MySQL中连接SQL语句中，ON子句的语法格式为：table1.column_name = table2.column_name。当模式设计对联接表的列采用了相同的命名样式时，就可以使用 USING 语法来简化 ON 语法，格式为：USING(column_name)。 所以，USING的功能相当于ON，区别在于USING指定一个属性名用于连接两个表，而ON指定一个条件。另外，SELECT *时，USING会去除USING指定的列，而ON不会。实例如下。

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
    SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId =t_type.id;
    +----+-------+--------+----+------+
    | id | title | typeId | id | name |
    +----+-------+--------+----+------+
    |  1 | aaa   |      1 |  1 | C++  |
    |  2 | bbb   |      2 |  2 | C    |
    |  7 | ggg   |      2 |  2 | C    |
    |  3 | ccc   |      3 |  3 | Java |
    |  6 | fff   |      3 |  3 | Java |
    |  4 | ddd   |      4 |  4 | C#   |
    |  5 | eee   |      4 |  4 | C#   |
    +----+-------+--------+----+------+
 
    SELECT * FROM t_blog INNER JOIN t_type USING(typeId);
    ERROR 1054 (42S22): Unknown column 'typeId' in 'from clause'
    SELECT * FROM t_blog INNER JOIN t_type USING(id); -- 应为t_blog的typeId与t_type的id不同名，无法用Using，这里用id代替下。
    +----+-------+--------+------------+
    | id | title | typeId | name       |
    +----+-------+--------+------------+
    |  1 | aaa   |      1 | C++        |
    |  2 | bbb   |      2 | C          |
    |  3 | ccc   |      3 | Java       |
    |  4 | ddd   |      4 | C#         |
    |  5 | eee   |      4 | Javascript |
    +----+-------+--------+------------+

自然连接：NATURE JOIN 自然连接就是USING子句的简化版，它找出两个表中相同的列作为连接条件进行连接。有左自然连接，右自然连接和普通自然连接之分。在t_blog和t_type示例中，两个表相同的列是id，所以会拿id作为连接条件。 另外千万分清下面三条语句的区别 。 自然连接:SELECT * FROM t_blog NATURAL JOIN t_type; 笛卡尔积:SELECT * FROM t_blog NATURA JOIN t_type; 笛卡尔积:SELECT * FROM t_blog NATURE JOIN t_type;

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
    SELECT * FROM t_blog NATURAL JOIN t_type;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog,t_type WHERE t_blog.id=t_type.id;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog INNER JOIN t_type ON t_blog.id=t_type.id;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog INNER JOIN t_type USING(id);
 
    +----+-------+--------+------------+
    | id | title | typeId | name       |
    +----+-------+--------+------------+
    |  1 | aaa   |      1 | C++        |
    |  2 | bbb   |      2 | C          |
    |  3 | ccc   |      3 | Java       |
    |  4 | ddd   |      4 | C#         |
    |  5 | eee   |      4 | Javascript |
    +----+-------+--------+------------+
 
    SELECT * FROM t_blog NATURAL LEFT JOIN t_type;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog LEFT JOIN t_type ON t_blog.id=t_type.id;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog LEFT JOIN t_type USING(id);
 
    +----+-------+--------+------------+
    | id | title | typeId | name       |
    +----+-------+--------+------------+
    |  1 | aaa   |      1 | C++        |
    |  2 | bbb   |      2 | C          |
    |  3 | ccc   |      3 | Java       |
    |  4 | ddd   |      4 | C#         |
    |  5 | eee   |      4 | Javascript |
    |  6 | fff   |      3 | NULL       |
    |  7 | ggg   |      2 | NULL       |
    |  8 | hhh   |   NULL | NULL       |
    |  9 | iii   |   NULL | NULL       |
    | 10 | jjj   |   NULL | NULL       |
    +----+-------+--------+------------+
 
    SELECT * FROM t_blog NATURAL RIGHT JOIN t_type;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog RIGHT JOIN t_type ON t_blog.id=t_type.id;
    SELECT t_blog.id,title,typeId,t_type.name FROM t_blog RIGHT JOIN t_type USING(id);
 
    +----+------------+-------+--------+
    | id | name       | title | typeId |
    +----+------------+-------+--------+
    |  1 | C++        | aaa   |      1 |
    |  2 | C          | bbb   |      2 |
    |  3 | Java       | ccc   |      3 |
    |  4 | C#         | ddd   |      4 |
    |  5 | Javascript | eee   |      4 |
    +----+------------+-------+--------+

补充 博客开头给出的第一张图除去讲了的内连接、左连接、右连接、外连接，还有一些特殊的韦恩图，这里补充一下。

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
    SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id
    WHERE t_type.id IS NULL;
    +----+-------+--------+------+------+
    | id | title | typeId | id   | name |
    +----+-------+--------+------+------+
    |  8 | hhh   |   NULL | NULL | NULL |
    |  9 | iii   |   NULL | NULL | NULL |
    | 10 | jjj   |   NULL | NULL | NULL |
    +----+-------+--------+------+------+
    SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id
    WHERE t_blog.id IS NULL;
    +------+-------+--------+----+------------+
    | id   | title | typeId | id | name       |
    +------+-------+--------+----+------------+
    | NULL | NULL  |   NULL |  5 | Javascript |
    +------+-------+--------+----+------------+
    SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id
    WHERE t_type.id IS NULL
    UNION
    SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id
    WHERE t_blog.id IS NULL;
    +------+-------+--------+------+------------+
    | id   | title | typeId | id   | name       |
    +------+-------+--------+------+------------+
    |    8 | hhh   |   NULL | NULL | NULL       |
    |    9 | iii   |   NULL | NULL | NULL       |
    |   10 | jjj   |   NULL | NULL | NULL       |
    | NULL | NULL  |   NULL |    5 | Javascript |
    +------+-------+--------+------+------------+

写完这篇博客发现有点“孔乙己：茴字的四种写法的感觉”，但还是有收获的。另外，等三面通知等的好急啊!! 引用 http://www.cnblogs.com/fudashi/p/6572101.html http://blog.csdn.net/wjc19911118/article/details/9716391 http://blog.csdn.net/taylor_tao/article/details/7068511 from:https://www.cnblogs.com/fudashi/p/7491039.html

龙生   07 Jan 2021
View Details

MYSQL解决 The total number of locks exceeds the lock table size 问题

当我们对Mysql 中的表进行大批量的分组，查询、 创建的时候经常会出现这个问题，他翻译过来大概是这个意思，总数已经超过锁定表的大小。一般出现这个原因会有两种可能： 1.MySQL的临时表过小也就是tmp_table_size tmp_table_size：它规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，默认如下图: 那么怎么改变临时表的大小呢？同样有两种方法 第一种是 使用命令行进行修改 先使用 show variables like "%tmp%";查看临时表的大小，我的是64M 然后使用SET GLOBAL  tmp_table_size =1024*1024*1024;我是给了1个G 第一种重启电脑后默认的设置会还原，适合临时使用一次的用户。或者我没有找到怎么设置永久的方法，如果有小伙伴们发现请告诉我哦， 第二种是修改mysql 的配置文件 1.关闭mysql 服务，方式是我的电脑——》管理->服务-》找到mysql的服务选择关闭。 2.找到mysql 的配置文件的位置，在前面的章节中我已经详细的介绍了这么找到Linux或者windows配置文件链接如下： https://blog.csdn.net/dingweiye123/article/details/80991341 3.注意配置文件要备份然后找到 tmp_table_size直接修改成tmp_table_size=3G 4.重启服务 2.MySQL的innod缓冲池过小也就是innodb_buffer_pool_size 和修改tmp_table_size的大小一样分为两种办法 1.mysql命令行设置 show variables like "%_buffer%";查看大小 使用SET GLOBAL innodb_buffer_pool_size=67108864;（3*1024*1024*1024，不要忘记;号）设置如下 第二种一样是修改配置文件 1.关闭mysql 服务，方式是我的电脑——》管理->服务-》找到mysql的服务选择关闭。 2.找到mysql 的配置文件的位置，在前面的章节中我已经详细的介绍了这么找到Linux或者windows配置文件链接如下： https://blog.csdn.net/dingweiye123/article/details/80991341 3.修改 innodb_buffer_pool_size的值 innodb_buffer_pool_size=3G 4.重启服务   from:https://blog.csdn.net/dingweiye123/article/details/80994576

龙生   05 Jan 2021
View Details

Windows批处理执行sql脚本/语句

脚本 SET dbhost=127.0.0.1 SET dbuser=root SET dbpasswd=xxxx SET dbName=dbnamexx SET sqlpath=./ set sqlfile=statistics_fee_data_sync.sql mysql -h%dbhost% -u%dbuser% -p%dbpasswd% --database=%dbName% < %sqlpath%%sqlfile% --default-character-set=utf8 @ECHO Done! ========================================================================================= 语句 SET dbhost=127.0.0.1 SET dbuser=root SET dbpasswd=xxxx SET dbName=dbnamexx SET sqlpath=./ set sqlfile=statistics_fee_data_sync.sql mysql -h%dbhost% -u%dbuser% -p%dbpasswd% %dbName% -e "select * from cityinfo2;select * from correct2 limit 100;"

龙生   04 Jan 2021
View Details

MySQL定时执行脚本(计划任务)命令实例

在mysql中我们可以直接进行一些参数设置让它成定时为我们执行一些任务了，这个虽然可以使用windows或者linux中的计划任务实现，但是mysql本身也能完成 查看event是否开启 复制代码 代码如下: show variables like '%sche%'; 将事件计划开启 复制代码 代码如下: set global event_scheduler =1;   创建存储过程test   复制代码 代码如下: CREATE PROCEDURE test () BEGIN update examinfo SET endtime = now() WHERE id = 14; END; 创建event e_test 复制代码 代码如下: create event if not exists e_test on schedule every 30 second on completion preserve do call test(); 每隔30秒将执行存储过程test,将当前时间更新到examinfo表中id=14的记录的endtime字段中去. 关闭事件任务 复制代码 代码如下: alter event e_test ON COMPLETION PRESERVE DISABLE; 开户事件任务 复制代码 代码如下: alter event e_test ON COMPLETION PRESERVE ENABLE; 以上测试均成功,测试环境为mysql 5.4.2-beta-community mysql community server(GPL) 以上的相关内容就是对MySQL定时执行的介绍，望你能有所收获。 mysql 计划任务重启后消失 我们只要修改一配置即可 event_scheduler在mysql的config中设置为OFF有关。去mysql中将配置改为ON则就搞定了。 更详细的大家可以往下看 MySQL5.1.x版本中引入了一项新特性EVENT，顾名思义就是事件、定时任务机制，在指定的时间单元内执行特定的任务，因此今后一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。 要查看当前是否已开启事件调度器，可执行如下SQL： SHOW VARIABLES LIKE […]

龙生   04 Jan 2021
View Details

mysql实现动态行转列

需求背景：在任务管理系统中，有任务详情表，每个任务下又分子任务节点，每个任务节点都有具体的跟进日期，包括开始时间结束时间，每天的任务完成进度。 有这样一个需求：在任务管理系统中根据任务节点id，展示每天的进度情况。 因为每个任务的时间范围不一样，所以需要用到动态的行转列，将时间周期作为动态的列，进度做为列的值。 任务节点表： quest_node_id：任务子节点id，follow_date:跟进日期 ，grade:进度百分比。 在mysql中创建存储过程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
BEGIN
 
 
 
SET @EE='';
 
SELECT b.* into @EE from ( SELECT @EE:=CONCAT(@EE,'SUM(IF(follow_date=\'',follow_date,'\'',',grade,0)) AS ',follow_date,',') col
 
FROM (SELECT DISTINCT follow_date FROM quest_follow_copy) A) b ORDER BY b.col desc limit 0,1 ;
 
SET @QQ=CONCAT('SELECT quest_follow_copy.quest_node_id AS columnA,',LEFT(@EE,LENGTH(@EE)-1),'
 
FROM quest_follow_copy GROUP BY quest_node_id');
 
PREPARE stmt2 FROM @QQ;
 
EXECUTE stmt2;
 
 
 
END

  注意：我发现follow_date的日期格式（如2018-08-10）会报错，存储过程执行不了，并且数字开头也不可以，所以我把日期改成了varchar类型，并在值里加了前缀。 执行结果如下： 某个节点在某天没有值则补零。 参考文档：mysql 动态行转列   from:https://blog.csdn.net/xiaoxiangzi520/article/details/81480477

龙生   22 Dec 2020
View Details

mysql行列转换

创建数据库、表

1
2
3
4
5
6
7
8
create database tests;
use tests;
create table t_score(
id int primary key auto_increment,
name varchar(20) not null,  #名字
Subject varchar(10) not null, #科目
Fraction double default 0  #分数
);

  添加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO `t_score`(name,Subject,Fraction) VALUES
         ('王海', '语文', 86),
        ('王海', '数学', 83),
        ('王海', '英语', 93),
        ('陶俊', '语文', 88),
        ('陶俊', '数学', 84),
        ('陶俊', '英语', 94),
        ('刘可', '语文', 80),
        ('刘可', '数学', 86),
        ('刘可', '英语', 88),
        ('李春', '语文', 89),
        ('李春', '数学', 80),
        ('李春', '英语', 87);

  方式一:使用if

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select name as 名字 ,
sum(if(Subject='语文',Fraction,0)) as 语文,
sum(if(Subject='数学',Fraction,0))as 数学,
sum(if(Subject='英语',Fraction,0))as 英语,
round(AVG(Fraction),2) as 平均分,
SUM(Fraction) as 总分
from t_score group by name    
union
select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
select 'TOTAL' as name,
sum(if(Subject='语文',Fraction,0)) as 语文,
sum(if(Subject='数学',Fraction,0))as 数学,
sum(if(Subject='英语',Fraction,0))as 英语,
SUM(Fraction) as 总分
from t_score group by Subject )t

  方式二:使用case

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select  name as Name,
sum(case when Subject = '语文' then Fraction end) as Chinese,
sum(case when Subject = '数学' then Fraction end) as Math,
sum(case when Subject = '英语' then Fraction end) as English,
sum(fraction)as score
from t_score group by name
UNION ALL
select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
select 'TOTAL' as name,
sum(case when Subject = '语文' then Fraction end) as Chinese,
sum(case when Subject = '数学' then Fraction end) as Math,
sum(case when Subject = '英语' then Fraction end) as English,
sum(fraction)as score
from t_score group by Subject)t

  方法三: with rollup

1
2
3
4
5
6
7
select
        ifnull(name,'TOll') name,
        sum(if(Subject='语文',Fraction,0)) as 语文,
       sum(if(Subject='英语',Fraction,0)) as 英语,
       sum(if(Subject='数学',Fraction,0))as 数学,
       sum(Fraction) 总分
        from t_score group by name with rollup

from:https://www.cnblogs.com/weibanggang/p/9679301.html

龙生   22 Dec 2020
View Details

MYSQL 数据库分区及取消分区

同事MM之前分享了数据库分区的知识，之前没认真研究过，结果现在自己的数据库资料越来越多，让强迫症的我特别难受，所以就对着同事写的SOP一步一步操作 。这里只介绍Range分区，因为我也是刚用到这一个 . 举个例子：要求将数据库dbemployeeinfo_copy 按ID分区，<3的为一个区，其它的为另一个区.，之后取消分区 原Table 的ID栏位为int格式： 实际生成的table在文件中的存储格式： 具体步骤如下： 1. 进入该table设置页，点击Partioning进行分区： 2.查看C盘该table位置，会发现原来的table已经分成两部分了，之后再增加资料，Part0的大小都不会发生变化了。   有时候会发现当初没考虑好，所以分区分错了，需要重新设置，所以第一反应就是把之前在Partitioning里设置的全部取消，然后提交就好了，但是会发现时无法提交的 这时候只能用指令解决了：alter table test.dbemployeeinfo_copy remove partitioning; 再去检查一下C盘对应文件夹里该table的资料，就没有分区了   from:https://blog.csdn.net/feelingdu/article/details/81914641

龙生   15 Dec 2020
View Details
1 … 5 6 7 … 40

Copyright © 2024 Longsheng All rights reserved