存在的记忆 - 数据库
type: channel
link: https://www.looaon.com/index.php/category/数据库/
items: 数据库常用Join查询
items: 导入大文件的时候没反应
items: mysql使用truncate清空表有外键问题
items: mysql替换指定字段中的字符串
items: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de
items: mysql5.7数据恢复
items: AWS RDS上如何使用mysqlbinlog命令下载binlog
items: AWS RDS for MySQL如何终止会话
items: mariadb 修改root用户密码
items: 1418 - you "might" want to use the less safe log bin trust function creators variable

数据库常用Join查询
type: item
date: 2022-07-09T10:46:41+08:00
description: 1.查询A，B表共有部分内容： SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key 2.查询A表所有和AB表共有部分： SELECT <select_list> FROM TableA A LEFT JOIN Table B B ON A.Key = B.Key 3.查询B表所有和AB表共有部分： SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key 4.查询A表独有部分内容： SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = TableB B ON A.Key = B.Key WHERE B.Key IS NULL 5.查询B表独有部分: SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL 6.查询AB表所有部分： SELECT <sclect_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key 7.查询AB表各自独有部分 SELECT<select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL

导入大文件的时候没反应
type: item
date: 2022-05-05T18:34:45+08:00
description: 用navacat导入大文件，导入到一半不动了报错，也有可能不报错MySQL server has gone away Table Restored: act_ge_bytearray Rolling back... Finished - Stopped before completion 问题原因：导入的数据超过了mysql默认单个字段最大容量查看数据库中最大的单个文件容量SHOW VARIABLES LIKE '%max_allowed_packet%';设置最大单个文件容量为10M，单次有效SET GLOBAL max_allowed_packet=2010241024 如果用phpstudy_pro，去mysql里性能设置-单数据包-，把这个值改大就OK

mysql使用truncate清空表有外键问题
type: item
date: 2021-01-21T21:36:21+08:00
description: （mysql 禁用和启用外键）mysql多个表之前存在外键关联时，清空数据报错，如下：TRUNCATE TABLE security_data > 1701 - Cannot truncate a table referenced in a foreign key constraint (`scb-dev`.`identify_result`, CONSTRAINT `identify_result_ibfk_2` FOREIGN KEY (`SECURITY_DATA_ID`) REFERENCES `scb-dev`.`security_data` (`ID`)) > 时间: 0.001s 解决方法：删除时先禁用外键，然后再启动外键-- 禁用外键约束.SET FOREIGN_KEY_CHECKS=0; 注意：这个语句只是会话级的，只能在这个会话操作导入数据，删除数据之类的操作。再打开新的会话，外键还是启用着生效的。-- 清除表内容，并且自动递增从1开始.truncate table campaigns -- 启动外键约束.SET FOREIGN_KEY_CHECKS=1; 注意：或者直接把当前会话关闭了，再新开会话，外键就是启用的-- 查看当前FOREIGN_KEY_CHECKS的值可用如下命令SELECT @@FOREIGN_KEY_CHECKS;@@FOREIGN_KEY_CHECKS=0（禁用外键）@@FOREIGN_KEY_CHECKS=1（启用外键）

mysql替换指定字段中的字符串
type: item
date: 2020-05-14T21:33:41+08:00
description: mysql 替换函数replace()实现mysql替换指定字段中的字符串mysql 替换字符串的实现方法:mysql中replace函数直接替换mysql数据库中某字段中的特定字符串，不再需要自己写函数去替换，用起来非常的方便。 mysql 替换函数replace()UPDATE `table_name` SET `field_name` = replace (`field_name`,'from_str','to_str') WHERE `field_name` LIKE '%from_str%' 说明：table_name —— 表的名字 field_name —— 字段名 from_str —— 需要替换的字符串 to_str —— 替换成的字符串 例如：mysql> SELECT REPLACE('www.looaon.com', 'www', 'http://looaon.com'); -> 'https://www.looaon.com' 该函数是多字节安全的,也就是说你不用考虑是中文字符还是英文字符.

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de
type: item
date: 2019-09-24T11:55:00+08:00
description: 今天往116上迁移数据库，报错1418- This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log bin trust function creators variable翻译：这个函数没有确定性，没有SQL，或者在声明中读取SQL数据，并且启用了二进制日志记录(您*可能*想要使用不太安全的log bin trust函数creator变量) 这是我们开启了bin-log, 我们就必须指定我们的函数是否是1 DETERMINISTIC 不确定的 2 NO SQL 没有SQl语句，当然也不会修改数据 3 READS SQL DATA 只是读取数据，当然也不会修改数据 4 MODIFIES SQL DATA 要修改数据 5 CONTAINS SQL 包含了SQL语句 其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。在MySQL中创建函数时出现这种错误的解决方法：set global log_bin_trust_function_creators=TRUE;

mysql5.7数据恢复
type: item
date: 2019-09-20T00:06:00+08:00
description: 2019-9-20 17:47:13 昨天需要手动追数据，一波骚操作后数据库起不来，切记不要在数据库启动时期手动删除数据库文件！数据库死了，但是数据文件还保存着，文件格式为idb和frm两种格式，位置一般在data里其中frm文件为数据库结构文件，idb为数据库数据文件如果结构有备份，文件丢了好搞，直接运行以下命令#释放空间 ALTER TABLE gk_task DISCARD TABLESPACE; #将源数据移动到目标数据 cp /date/gass/gk_task.ibd ./ 更改目标库的用户组，权限给640就行 chown -R mysql:mysql gass/ 重新加载数据 ALTER TABLE gk_task IMPORT TABLESPACE; 如果不行数据结构也丢了，有款神器mysqlfrm支持windows和linux点击下载1、mysqlfrm 是一个恢复性质的工具，用来读取.frm文件并从该文件中找到表定义数据生成CREATE语句。 下载链接：https://downloads.mysql.com/archives/utilities/由于mysqlfrm是mysql-utilities工具一部分，我们在Windows里面安装mysql-utilities即可；在安装目录下，会有各种命令； centos7安装：(1) rpm -ivh mysql-connector-python-2.1.7-1.el7.x86_64 #这里用mysql-connector-python-2.1.7 链接地址：https://downloads.mysql.com/archives/get/file/mysql-connector-python-2.1.7-1.el7.x86_64.rpm (2) rpm -ivh mysql-utilities-1.6.5-1.el7.noarch.rpm 链接地址：https://downloads.mysql.com/archives/get/file/mysql-utilities-1.6.5-1.el7.noarch.rpm 注意： 尽量不要通过yum来安装mysql-utilities；而且mysql-utilities 1.6.5和mysql-connector-python 8.0.12在一起有问题！ windows 使用语法mysqlfrm --server=root:root@127.0.0.1:3306 d:\gass\gk_crawl_site_product.frm --port=3434 --user=mysql --diagnostic linux使用语法/date/gass mysqlfrm --server=root:30b65ef5307e5e45@127.0.0.1:13307 ./gk_template_ext.frm --port=3434 --user=mysql --diagnostic 参考链接https://www.cnblogs.com/ivictor/p/9302069.htmlhttps://blog.csdn.net/wearegouest/article/details/79538618https://www.longlong.asia/2015/11/21/mysql-restore-ibd.htmlhttps://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

AWS RDS上如何使用mysqlbinlog命令下载binlog
type: item
date: 2019-06-25T22:28:05+08:00
description: AWS RDS上，可以使用mysqlbinlog来下载binlog，具体命令为：mysqlbinlog \ --read-from-remote-server \ --host=db-endpoint \ --port=3306 \ --user UserName \ --password \ --raw \ --result-file=/tmp/ \ binlog.xxxxx 需要注意Amazon RDS会随时清除binlog内容，并不会持久的保持binlog在数据库实例上，因此如果您可能需要使用 mysql.rds_set_configuration 这个存储过程来修改保持数据库保持binlog的时间，以便您有足够的时间下载binlog。在更改binlog保留时间后，请注意您数据库实例的存储空间，以确保binlog不会占用太多存储空间。 如果需要设置binlog的轮转时间为1天call mysql.rds_set_configuration('binlog retention hours', 24); 通过以下命令查看现在的轮转周期是多少call mysql.rds_show_configuration;

AWS RDS for MySQL如何终止会话
type: item
date: 2019-06-25T22:26:00+08:00
description: mysql.rds_kill_query_id 终止针对MySQL或MariaDB服务器运行的查询。语法CALL mysql.rds_kill_query_id(queryID); 参数queryID 整数。将终止的查询的标识。使用说明要终止针对 MariaDB 服务器运行的查询，请使用 mysql.rds_kill_query_id 过程并传入该查询的 ID。要获取查询 ID，如下所示：SELECT USER, HOST, COMMAND, TIME, STATE, INFO, QUERY_ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = '<user name> 以下示例终止查询 ID 为 11195102 的查询：call mysql.rds_kill_query_id(11195102); 批量kill session的语句:select concat('CALL mysql.rds_kill(',id,');') from information_schema.processlist where user='gwmmm'; select concat('CALL mysql.rds_kill(',trx_mysql_thread_id,');') from information_schema.innodb_trx; CALL mysql.rds_kill( 11195230); CALL mysql.rds_kill( 11195102); CALL mysql.rds_kill( 11195114); CALL mysql.rds_kill( 11195108); CALL mysql.rds_kill( 11195179); CALL mysql.rds_kill( 11195224); CALL mysql.rds_kill( 11195214); CALL mysql.rds_kill( 11194577); CALL mysql.rds_kill( 11195476); CALL mysql.rds_kill( 11194995); CALL mysql.rds_kill( 11195187); CALL mysql.rds_kill( 11195022); CALL mysql.rds_kill( 11195319);

mariadb 修改root用户密码
type: item
date: 2019-04-13T13:59:00+08:00
description: 一、修改用户密码，以root为例知道root密码，需要修改方法a. 登入数据库来修改# mysql -uroot -p /*输入密码进入*/ /*第一个方式：直接编辑数据库字段*/ MariaDB [(none)]> use mysql; MariaDB [mysql]> UPDATE user SET password=password('newpassword') WHERE user='root'; MariaDB [mysql]> flush privileges; MariaDB [mysql]> exit /*第二个方式：修改密码，不用进入mysql*/ MariaDB [(none)]> SET password for 'root'@'localhost'=password('newpassword'); MariaDB [(none)]> exit;

1418 - you "might" want to use the less safe log bin trust function creators variable
type: item
date: 2019-04-12T19:10:44+08:00
description: 问题原因：因为CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,ALTER FUNCTION,CALL, DROP PROCEDURE, DROP FUNCTION等语句都会被写进二进制日志,然后在从服务器上执行。但是，一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的，在从服务器上执行(相对与主服务器是重复执行)可能会造成恢复的数据与原始数据不同，从服务器不同于主服务器的情况。解决办法也有两种第一种是在创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个，例如:CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`() DETERMINISTIC BEGIN #Routine body goes here... END;; 第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。设置方法有三种:1.在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1; 2.MySQL启动时，加上–log-bin-trust-function-creators选贤，参数设置为1 3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1