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()begindeclare exit handler for sqlexception rollback;set @curMonth=month(now());if( @curMonth=3 or @curMonth=6 or @curMonth=9 or @curMonth=12 ) thenstart TRANSACTION;select REPLACE( partition_name, 'p', '') into @pMaxName from information_schema.partitionswhere 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 BEGINIF new.did IS NOT NULL THENUPDATE `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 BEGINIF old.did IS NOT NULL THENUPDATE `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 BEGINIF new.did <> old.did THENIF old.did IS NOT NULL THENUPDATE sy_docs SET us=us-1 WHERE id=old.did;END IF;IF new.did IS NOT NULL THENUPDATE sy_docs SET us=us+1 WHERE id=new.did;END IF;END IF;END;;DELIMITER ;