MySQL学习日志

一、常用sql

1、查看触发器

 SELECT TRIGGER_NAME FROM information_schema.TRIGGERS;

2、删除触发器

drop trigger JFXX_del_SCNODECONFIG;

3、创建触发器

CREATE TRIGGER JFXX_del_SCTEACHER BEFORE DELETE ON SCTEACHER FOR EACH ROW
BEGIN
IF (OLD.deptId = 261) THEN
	INSERT INTO JFXX.SCTEACHER (TEACHERID, SYNACTION_)
VALUES
	(OLD.TEACHERID, 2);
END IF;
END

4、AWS RDS创建触发器失败

提示错误：
1419 - You do not have the SUPER privilege and binary logging is enabled
解决方法：
登录AWS控制台 → RDS → 数据库实例 → 参数组 →
搜索log_bin_trust_function_creators → 编辑参数 → 将值设置成1 → 保存

5、查看所有数据库

show databases;
//删除数据库
delete database databasename;
//切换数据库
use databasename;

6、查看所有表

show tables;

7、字符集

https://blog.csdn.net/u012410733/article/details/61619656/
https://www.jb51.net/article/110395.htm
查看数据库字符集

show variables like ‘%char%’;

查看表字符集编码
注意：修改数据库字符集不会更改已经创建的对象，例如表、函数等的字符集

show create table PFUSER;

二、常见错误

1、This version of MySQL doesn’t yet support 'multiple triggers with the same action time and event for one table

需要5.7.16版本及以上才允许创建多个触发器

2、支持Emoji表情

在微信公众号开发过程中，经常遇到存储Emoji表情的需求，需要将编码从utf8转换成utf8mb4。前提条件是MySQL的版本大于5.5,3才支持utf8mb4编码。

使用命令行连接MySQL

mysql -hhostname -uusername -ppassword

修改某个表的字段字符集：

ALTER TABLE table_name CHANGE column_name  VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查看表的字符集

show create table TABLE_NAME;  

CSOCLASSTRACK | CREATE TABLE `csoclasstrack` (
  `TRACKID` int(9) NOT NULL,
  `SCHOOLID` int(9) DEFAULT NULL,
  `MOUSERID` int(9) DEFAULT NULL,
  `TEACHERNAME` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TEACHERID` int(9) DEFAULT NULL,
  `ISSUETIME` datetime DEFAULT NULL,
  `CONTENT` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TYPE` smallint(1) DEFAULT NULL COMMENT '0:Open,1:Class',
  `DELETETAG` int(1) DEFAULT NULL,
  `SUPPORT` tinyint(1) DEFAULT NULL,
  `CLASSENAMES` varchar(4000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`TRACKID`),
  KEY `IX_CSOCLASSTRACK_SCHOOLID` (`SCHOOLID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci