7.3、实例操作介绍
7.3.1、创建一个表
CREATE TABLE `t_user` ( `user_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键', `user_name` varchar(5) DEFAULT NULL COMMENT '用户名', `age` int(3) DEFAULT NULL COMMENT '年龄', PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
7.3.2、插入数据
INSERT INTO t_user (user_name, age) VALUES('张三',24),('李四',25),('王五',26),('赵六',27);
7.3.3、创建函数
-- 创建一个函数 DELIMITER $$ -- 开始创建函数 CREATE FUNCTION user_function(v_id INT) RETURNS VARCHAR(50) READS SQL DATA DETERMINISTIC BEGIN -- 定义变量 DECLARE userName VARCHAR(50); -- 给定义的变量赋值 SELECT user_name INTO userName FROM t_user WHERE user_id = v_id; -- 返回函数处理结果 RETURN userName; END; -- 函数创建定界符 DELIMITER ;
7.3.4、调用函数
//查询用户ID为1的信息 SELECT user_function(1);
7.3.5、删除函数
DROP FUNCTION IF EXISTS user_function;
八、存储过程
8.1、创建语法
CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
过程与创建函数类似，其中的声明语句结束符，可以自定义:
DELIMITER $$ 或 DELIMITER //
参数说明：
- IN 输入参数：表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
- OUT 输出参数：该值可在存储过程内部被改变，并可返回
- INOUT 输入输出参数：调用时指定，并且可被改变和返回
创建一个查询用户信息的存储过程示例：
DELIMITER $$ CREATE PROCEDURE user_procedure(IN v_id int,OUT userName varchar(255)) BEGIN SELECT user_name as userName FROM t_user WHERE user_id = v_id; END $$ DELIMITER ;
8.2、存储过程调用
-- @out为输出参数 CALL user_procedure(1, @out);
输出结果：
张三
8.3、存储过程删除
DROP PROCEDURE [IF EXISTS] proc_name;
删除示例：
DROP PROCEDURE IF EXISTS user_procedure;
8.4、存储过程和函数的区别
- 函数只能通过return语句返回单个值或者表对象。而存储过程不允许执行return，但是通过out参数返回多个值。
- 函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。
- 函数限制比较多，比如不能用临时表，只能用表变量，还有一些函数都不可用等等，而存储过程的限制相对就比较少
- 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
- 当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。
九、触发器
触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。
9.1、创建触发器
定义语法
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
参数说明：
- FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。
- trigger_time：BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后。
- tigger_event详解：
- INSERT 型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中，相当与一系列的INSERT操作)；
- UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；
- DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。
- trigger_order：是MySQL5.7之后的一个功能，用于定义多个触发器，使用follows(尾随)或precedes(在…之先)来选择触发器执行的先后顺序。
示例，创建了一个名为trig1的触发器，一旦在t_user表中有插入动作，就会自动往t_time表里插入当前时间。
CREATE TRIGGER trig1 AFTER INSERT ON t_user FOR EACH ROW INSERT INTO t_time VALUES(NOW());
创建有多个执行语句的触发器语法
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW BEGIN 执行语句列表 END;
示例如下：
DELIMITER // CREATE TRIGGER trig2 AFTER INSERT ON t_user FOR EACH ROW BEGIN INSERT INTO t_time VALUES(NOW()); INSERT INTO t_time VALUES(NOW()); END// DELIMITER ;
一旦插入成功，就会执行BEGIN ...END语句！
9.2、查询触发器
- 查询所有触发器
SHOW TRIGGERS;
- 查询指定的触发器
select * from information_schema.triggers where trigger_name='trig1';
所有触发器信息都存储在information_schema数据库下的triggers表中，可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询。
9.3、删除触发器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
示例如下：
DROP TRIGGER IF EXISTS trig1
删除触发器之后最好使用上面的方法查看一遍。
9.4、总结
触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。
10、序列
在 MySQL 中，可以有如下几种途径实现唯一值：
- 自增序列
- 程序自定义
- UUID() 函数
- UUID_SHORT() 函数
10.1、自增序列
在mysql中，一般我们可以给某个主键字段设置为自增模式，例如：
#创建一个表test_db，字段内容为id,name create table test_db(id int,name char(10)); # 设置id主键 alter table test_db add primary key(id); # 将id主键设置为自增长模式 alter table test_db modify id int auto_increment;
这种模式，在单库单表的时候，没啥问题，但是如果要对test_db表进行分库分表，这个时候问题就来了，如果水平分库，这个时候向test_db_1、test_db_2中插入数据，就会出现相同的ID！
10.2、程序自定义
当然，为了避免出现这种情况，有的大神就自己单独创建了一张自增序列表，单独维护，这样就不会出现在分表的时候出现相同的ID！
