mysql备忘

一、配置

[client]
		# UTF8编码
		default-character-set = utf8
[wampmysqld]
		# 英文提示
		lc-messages=en_US
		# 开启事件处理
		event_scheduler = 1

[mysqld]
		# UTF8编码
		character-set-server = utf8

二、修改root口令

mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;

三、数据目录迁移

1、首先停止服务；

2、复制源数据目录到新的目录；

3、修改配置文件：

[wampmysqld]
datadir=e:/newDatapath

4、重启服务。

四、导出数据库

mysqldump -u root -p --opt -R -E hx_test > e:\abc.sql

五、增加外键约束

alert table xxx add foreign key (`key`) references table( `key` ) on delete cascade on update cascade;

六、查看系统变量配置

show variables like 'xxx%'

七、创建存储过程

DELIMITER ;;
CREATE PROCEDURE P_SetGSPartition()
begin
	declare exit handler for sqlexception rollback;
	set @curMonth=month(now());
	if( @curMonth=3 or @curMonth=6 or @curMonth=9 or @curMonth=12 ) then
		start TRANSACTION;
		select REPLACE( partition_name, 'p', '') into @pMaxName from information_schema.partitions
			where table_schema='botj_kaohe' and table_name='gs_yue' order by partition_ordinal_position desc limit 1;
		set @pNxtName= replace( date(date_add(@pMaxName,interval 3 month )), '-','');
		set @sl = concat( 'alter table gs_yue add partition ( partition p', @pNxtName, ' values less then( to_days(''',@pNxtName, ''')))');
		prepare stm from @sl;
		execute stm;
		deallocate prepare stm;
		commit;
	end if;
end;;
DELIMITER ;

八、创建事件

CREATE EVENT E_SetPartitions ON SCHEDULE EVERY 1 MONTH STARTS '2015-12-01 04:00:00' DO CALL P_SetGSPartition();

九、创建触发器

CREATE TABLE `sy_users` (
  `id` varchar(16) NOT NULL COMMENT 'ID',
  `pwd` varchar(32) NOT NULL COMMENT '密码',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `sex` tinyint(3) NOT NULL COMMENT '性别',
  `rid` int(11) NOT NULL COMMENT '角色ID',
  `bid` int(11) NOT NULL COMMENT '所属机构ID',
  `gid` int(11) NOT NULL COMMENT '岗位ID',
  `bgn` varchar(64) DEFAULT NULL COMMENT '布谷鸟ID',
  `email` varchar(128) DEFAULT NULL COMMENT '电子邮件',
  `tel` varchar(64) DEFAULT NULL COMMENT '电话',
  PRIMARY KEY (`id`),
  UNIQUE KEY `bgn` (`bgn`),
  UNIQUE KEY `email` (`email`),
  KEY `rid` (`rid`),
  KEY `bid` (`bid`),
  KEY `gid` (`gid`),
  KEY `pwd` (`pwd`),
  CONSTRAINT `sy_users_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `sy_role` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `sy_users_ibfk_2` FOREIGN KEY (`bid`) REFERENCES `sy_branch` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `sy_users_ibfk_3` FOREIGN KEY (`gid`) REFERENCES `sy_work` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sy_docs` (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '文档ID',
	`dt` date NOT NULL COMMENT '上传日期',
	`us` int(11) NOT NULL DEFAULT '0' COMMENT '引用计数',
	`pt` varchar(260) DEFAULT NULL COMMENT '文档路径',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sy_udate` (
  `uid` varchar(16) NOT NULL COMMENT '用户ID',
  `xz` tinyint(3) NOT NULL COMMENT '用工性质',
  `dth` date DEFAULT NULL COMMENT '签订合同日期',
  `qxh` int(11) NOT NULL DEFAULT '0' COMMENT '合同期限（年）',
  `did` int(11) DEFAULT NULL COMMENT '合同文档ID',
  `lgd` date DEFAULT NULL COMMENT '最近轮岗日期',
  `xjd` date DEFAULT NULL COMMENT '最近休假日期',
  PRIMARY KEY (`uid`),
  CONSTRAINT `sy_udate_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `sy_users` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `sy_udate_ibfk_2` FOREIGN KEY (`did`) REFERENCES `sy_docs` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER ;;
CREATE TRIGGER `tg_iud` AFTER INSERT ON `sy_udate` FOR EACH ROW BEGIN
	IF new.did IS NOT NULL THEN
		UPDATE `sy_docs` SET us=us+1 WHERE id=new.did;
	END IF;
end;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `tg_dud` AFTER DELETE ON `sy_udate` FOR EACH ROW BEGIN
	IF old.did IS NOT NULL THEN
		UPDATE `sy_docs` SET `us`=`us`-1 WHERE `id`=old.did;
	END IF;
end;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `tg_uud` AFTER UPDATE ON `sy_udate` FOR EACH ROW BEGIN
	IF new.did <> old.did THEN
		IF old.did IS NOT NULL THEN
			UPDATE sy_docs SET us=us-1 WHERE id=old.did;
		END IF;
		IF new.did IS NOT NULL THEN
			UPDATE sy_docs SET us=us+1 WHERE id=new.did;
		END IF;
	END IF;
END;;
DELIMITER ;