mysql

mysql只有这个InnoDB驱动是支持事务处理的，默认MyIsAM驱动不支持.

mysql增加列字段

#本地oa 组织部门增加钉钉父级id
ALTER table department add COLUMN DINGDING_DEPT_PARENT INT(11) DEFAULT 0;

# 本地oa用户增加绑定钉钉id字段
ALTER table user add column DING_USERIDS VARCHAR(60) DEFAULT 0;

mysql创建语句

create database form; 
use form; 
CREATE TABLE `message` ( 
`id` tinyint(1) NOT NULL auto_increment, 
`user` varchar(25) NOT NULL, 
`title` varchar(50) NOT NULL, 
`content` tinytext NOT NULL, 
`lastdate` date NOT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=1 ; 

CREATE TABLE `t1` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `remark` varchar(255) DEFAULT NULL,
  `ctime` date DEFAULT '1900-01-01' COMMENT '日期',
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

mysql插入语句

INSERT INTO data.admin (id ,zhanghao ,mima )VALUES ('1', 'admin', 'admin')

mysql增加索引

# 钉钉id字段建立索引，增加查询速度
ALTER  TABLE  `user`  ADD  INDEX DING_USERIDS (`DING_USERIDS`);

一个mysql存储过程事务例子

delimiter //
drop procedure if exists test_insert;
create procedure test_insert()

BEGIN

DECLARE p_count int DEFAULT 0;
DECLARE copy_o_id int DEFAULT 0;
DECLARE insert_newequipment_id int DEFAULT 0;
DECLARE insert_copy_info_id int DEFAULT 0;
DECLARE insert_test_store_id int DEFAULT 41;# 分店  12 月份   12 月份 分店-----------
DECLARE insert_test_num int DEFAULT 140;# 个数-----------

DECLARE t_error INTEGER DEFAULT 0;    
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; 

start transaction; #--整个存储过程指定为一个事务 
WHILE p_count < insert_test_num DO
#插入默认码表状态设置
INSERT INTO e_copy_option
(create_time,delete_status,update_time,can_clearing_copy,can_clearing_scale,game_coin_copy,base_copy,green_ticket_copy,lottery_ticket_copy,prize_copy)
values(now(),'Valid',now(),-1,0,-1,0,-1,-1,0);
select last_insert_id() into copy_o_id;
select '1已插入默认码表设置？' as title;
#插入门店机台清单
insert into e_store_equipment(create_time,delete_status,update_time,creator,equipment_info_id,equipment_info_img,equipment_info_name,equipment_numbering,equipment_sequence,equipment_status,is_use_copy,qr_code_url,s_equipment_name,second_category_id,second_category_name,store_equipment_remark,copy_option_id,store_id)
values(now(),'Valid',now(),'admin1',14,'','力达小爪机',concat('v2autoinfo',copy_o_id),copy_o_id,'Disable',0,'','力达小爪机',11,'抓娃娃机','',copy_o_id,insert_test_store_id);
select last_insert_id() into insert_newequipment_id;
select '2已插入门店设备清单' as title;

#插入e_copy_info_record-----说明该机台码表已经配置好了??
insert into e_copy_info_record_201912(create_time,delete_status,update_time,equipment_info_name,equipment_numbering,s_equipment_name,s_equipment_id,store_id)
values(now(),'Valid',now(),'力达小爪机',concat('v2autoinfo',copy_o_id),concat('力达小爪机#',copy_o_id),insert_newequipment_id,insert_test_store_id);
select last_insert_id() into insert_copy_info_id;# 已配置码表id？
select '3.1 已完成码表初始化配置，可以进行抄分录入' as title;

#插入具体录分表所包含码表的配置信息------入币码表    出礼品码表
insert into e_copy_detail_record_201912(create_time,delete_status,update_time,auto_value,copy,copy_info_id,copy_type,device_position,is_auto,is_handmade,manual_value,position_type,prize_show,reset_value,s_equipment_id)
values(now(),'Valid',now(),0,0,insert_copy_info_id,'Base','1P',1,0,0,'Common',1,0,insert_newequipment_id)
,(now(),'Valid',now(),0,0,insert_copy_info_id,'Prize','1P',1,0,0,'Common',1,0,insert_newequipment_id);
select '3.2生成录分码表的配置项' as title;
#插入当天录分表----- 已生效的机台是 0点定时任务做的， 其他第一次手动启用的是启用时插入
insert into e_copy_day_report(create_time,delete_status,update_time,can_clearing_scale,currency,currency_rate,equipment_info_name,equipment_numbering,first_category_name,input_coin_count,out_can_clearing_count,out_coin_count,out_green_ticket_count,out_lottery_ticket_count,out_prize_count,revenue,s_equipment_id,s_equipment_name,second_category_name,theory_real_coin,ticket_value,store_id)
values(now(),'Valid',now(),0,0,0,'力达小爪机',concat('v2autoinfo',copy_o_id),'娃娃机',0,0,0,0,0,0,0,insert_newequipment_id,concat('力达小爪机#',copy_o_id),'抓娃娃机',0,0,insert_test_store_id);
select '4 生成录分栏目显示' as title;
#前面生成档案的默认码表配置什么都没有， 后面需要设置成娃娃机基本的配置(有基础抄分项，有出礼品项)
update e_copy_option set base_copy=1,prize_copy=1 where id=copy_o_id;
#启用设备---status状态标记为正常/停用, is_use_copy=1 表示已经初始化过码表了
update e_store_equipment set equipment_status='Normal',is_use_copy=1 where id=insert_newequipment_id;

#保存码表数据---- 只有录入码表数据后，才会插入e_day_report_detial_201912表中进行保存
SET p_count = p_count + 1;
END WHILE;

#commit; #-- 语句1。必须主动提交
 IF t_error = 1 THEN
            ROLLBACK;
        ELSE
            COMMIT;
        END IF;
   select t_error;   #返回标识位的结果集；
END
//
call test_insert();