mysql笔记

新版的mysql user表password字段变成authentication_string

重新设置主键顺序
alter table pd_dict auto_increment=57

#开启日志设置
在mysql安装目录新建log目录
D:mysqllog
修改配置文件：
D:mysqldatamy.ini
# General and Slow logging.
log-output=FILE
general-log=1
general_log_file="D:mysqlloglog-mysql.log"
slow-query-log=1
slow_query_log_file="D:mysqllogmysql-slow.log"
long_query_time=10
# Binary Logging.
# log-bin
log_bin=D:mysqllogmysql-bin.log
# Error Logging.
log-error="D:mysqllogmysql.err"
重启mysql

#创建与修改存储过程
1 创建
delimiter $$
CREATE PROCEDURE delFK(in db_name varchar(50))
BEGIN
DECLARE fk_name varchar(64);
DECLARE sqlcmd varchar(255);
DECLARE table_name varchar(64);
DECLARE cur_ok bool DEFAULT false;
DECLARE cur CURSOR for SELECT a.CONSTRAINT_NAME,b.TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS a,information_schema.TABLES b WHERE a.TABLE_NAME=b.TABLE_NAME AND b.TABLE_SCHEMA = db_name;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET cur_ok=false;
open cur;
REPEAT
FETCH cur INTO fk_name,table_name;
IF NOT cur_ok THEN
set @sqlcmd=CONCAT('use ',db_name,'; alter table ', table_name, ' drop foreign key ', fk_name);
SELECT @sqlcmd;
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL cur_ok END REPEAT;
close cur;

END $$
delimiter ;

2 修改
目前不支持修改语句（太弱智），只能先删除再建
DELIMITER $$
DROP PROCEDURE IF EXISTS delFK;
CREATE PROCEDURE delFK(in db_name varchar(50))
BEGIN
DECLARE fk_name varchar(64);
DECLARE sqlcmd varchar(255);
DECLARE table_name varchar(64);
DECLARE cur_ok bool DEFAULT false;
DECLARE cur CURSOR for SELECT a.CONSTRAINT_NAME,b.TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS a,information_schema.TABLES b WHERE a.TABLE_NAME=b.TABLE_NAME AND b.TABLE_SCHEMA = db_name;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET cur_ok=false;
open cur;
REPEAT
FETCH cur INTO fk_name,table_name;
IF NOT cur_ok THEN
set @sqlcmd=CONCAT('use ',db_name,'; alter table ', table_name, ' drop foreign key ', fk_name);
SELECT @sqlcmd;
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL cur_ok END REPEAT;
close cur;

END $$
delimiter ;

#类型映射
MySQL数据类型 JAVA数据类型 JDBC TYPE 普通变量类型 主键类型
BIGINT Long BIGINT 支持 支持
TINYINT Byte TINYINT 支持 不支持
SMALLINT Short SMALLINT 支持 不支持
MEDIUMINT Integer INTEGER 支持 支持
INTEGER Integer INTEGER 支持 支持
INT Integer INTEGER 支持 支持
FLOAT Float REAL 支持 不支持
DOUBLE Double DOUBLE 支持 不支持
DECIMAL BigDecimal DECIMAL 支持 不支持
NUMERIC BigDecimal DECIMAL 支持 不支持
CHAR String CHAR 支持 不支持
VARCHAR String VARCHAR 支持 不支持
TINYBLOB DataTypeWithBLOBs.byte[] BINARY 不支持 不支持
TINYTEXT String VARCHAR 支持 不支持
BLOB DataTypeWithBLOBs.byte[] BINARY 不支持 不支持
TEXT DataTypeWithBLOBs.String LONGVARCHAR 不支持 不支持
MEDIUMBLOB DataTypeWithBLOBs.byte[] LONGVARBINARY 不支持 不支持
MEDIUMTEXT DataTypeWithBLOBs.String LONGVARCHAR 不支持 不支持
LONGBLOB DataTypeWithBLOBs.byte[] LONGVARBINARY 不支持 不支持
LONGTEXT DataTypeWithBLOBs.String LONGVARCHAR 不支持 不支持
DATE Date DATE 支持 不支持
TIME Date TIME 支持 不支持
YEAR Date DATE 不支持 不支持
DATETIME Date TIMESTAMP 支持 不支持
TIMESTAMP Date TIMESTAMP 支持 不支持
---------------------

#查看引擎
show engines;

#查看版本
mysql -V

#解决linux下mysql表名区分大小写的问题
原来Linux下的MySQL默认是区分表名大小写的，通过如下设置，可以让MySQL不区分表名大小写：
1、用root登录，修改 /etc/my.cnf；
2、在[mysqld]节点下，加入一行： lower_case_table_names=1
3、重启MySQL即可；
其中 lower_case_table_names=1 参数缺省地在 Windows 中这个选项为 1 ，在 Unix 中为 0

#mysql备份：
1 脚本备份:
rem auther:cjh
rem date:20171030
rem ******MySQL backup start********
@echo off

forfiles /p ".db_backup" /m kintech_pd_backup_*.sql /d -7 /c "cmd /c del /f @path"

set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%"
cd c:mysqlin
mysqldump --opt --single-transaction=TRUE --user=root --password=Helka1234!@#$ --host=192.168.1.250 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "kintech_pd" > C:UsersdellDesktopmysql备份kintech_pd_backup_%Ymd%.sql

@echo on
rem ******MySQL backup end********

2 navicat备份
navicat备份计划默认存储路径：
存储路径：我的文档 -> Navicat -> MySQL -> servers ->相应的连接名
如果要修改则关闭连接，则编辑连接，高级，设置位置

#备份数据库
mysqldump -hlocalhost -uroot -proot kintech_pd>kintech_pd.sql
备份数据库表
mysqldump -hlocalhost -uroot -proot kintech_pd pd_dict>pd_dict.sql

#还原数据库
mysql -hlocalhost -uroot -proot kintech_pd < kintech_pd.sql
#还原数据库表
mysql -hlocalhost -uroot -proot kintech_pd < pd_dict.sql

#函数使用：
group_concat(TypeNameCN SEPARATOR ',') 等同 group_concat(TypeNameCN)
select INSTR('123','2') ==2
select FIND_IN_SET('2','1,2,3') ==2
select CONCAT(NULL,6) 结果为NULL

设置密码时，去掉验证
plugin-load=validate_password.so
validate-password=OFF

登录跳过密码
skip-grant-tables
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

开启二进制日志后必须设置，要不不能创建函数与过程
log_bin_trust_function_creators=1

高版本的mysql默认开启了only_full_group_by（和oracle排序相似）
select @@global.sql_mode;
修改/etc/my.cnf 添加
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

垂直输出信息
show slave status G