/*
Navicat MySQL Data Transfer

Source Server         : 127.0.0.1
Source Server Version : 50714
Source Host           : 127.0.0.1:3306
Source Database       : game

Target Server Type    : MYSQL
Target Server Version : 50714
File Encoding         : 65001

Date: 2019-05-08 22:42:26
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `createTime` datetime DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `pass` varchar(255) DEFAULT NULL,
  `role` int(11) DEFAULT NULL,
  `game` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of account
-- ----------------------------

-- ----------------------------
-- Table structure for accounts
-- ----------------------------
DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(32) NOT NULL,
  `user_pwd` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for cdkeycode
-- ----------------------------
DROP TABLE IF EXISTS `cdkeycode`;
CREATE TABLE `cdkeycode` (
  `id` bigint(16) unsigned NOT NULL,
  `sort` varchar(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `giftId` int(8) unsigned zerofill DEFAULT NULL,
  `roleId` varchar(255) DEFAULT NULL,
  `accountId` varchar(255) DEFAULT NULL,
  `accountActive` int(8) unsigned zerofill DEFAULT NULL,
  `roleIdActive` int(8) unsigned zerofill DEFAULT NULL,
  `type` int(4) unsigned zerofill DEFAULT NULL,
  `count` int(8) DEFAULT NULL,
  `begintime` datetime DEFAULT NULL,
  `endtime` datetime DEFAULT NULL,
  `status` int(8) unsigned zerofill DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_cdkey` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of cdkeycode
-- ----------------------------

-- ----------------------------
-- Table structure for operation
-- ----------------------------
DROP TABLE IF EXISTS `operation`;
CREATE TABLE `operation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `operationname` varchar(50) DEFAULT NULL,
  `role` int(4) DEFAULT NULL,
  `method` varchar(50) DEFAULT NULL,
  `status` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for serverlist
-- ----------------------------
DROP TABLE IF EXISTS `serverlist`;
CREATE TABLE `serverlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game` int(11) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `sid` varchar(255) DEFAULT NULL,
  `sname` varchar(255) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_ec2wkwe9649pvn1hj8r5a0xma` (`ip`),
  UNIQUE KEY `UK_qj449dcdylj5webkfp9m3tljw` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of serverlist
-- ----------------------------

-- ----------------------------
-- Procedure structure for INSERTCDKEY
-- ----------------------------
DROP PROCEDURE IF EXISTS `INSERTCDKEY`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERTCDKEY`(in n int,in name varchar(255),in sort varchar(255),in giftId int,in begintime datetime,in endtime datetime)
BEGIN
DECLARE NM int;#�����
DECLARE RW int DEFAULT 100000;#�������
DECLARE IDINDEX int;#��CDKeycode����id
DECLARE IDTMPINDEX int;#��t_ids����id
DECLARE INDEXX int;#��ֹ��ѭ��

SET RW = n;


DROP TABLE IF EXISTS t_ids;
CREATE temporary TABLE t_ids(
  id bigint(16) unsigned NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;
#ɾ������δ��ȡ��cdkey
DELETE FROM CDKeycode WHERE status =0 AND endtime<NOW();
outer_label:  BEGIN  #����һ�����
WHILE RW > 0 DO
	SET INDEXX = INDEXX + 1;
	IF INDEXX > 100000000 THEN
			LEAVE  outer_label;  #������������ֹѭ������ת��end outer_label���
  END IF;
	SET NM = (SELECT FLOOR(10000000 + (RAND() * 89999999)));
	SET IDINDEX = (SELECT count(id) FROM CDKeycode WHERE id = NM);
	SET IDTMPINDEX = (SELECT count(id) FROM t_ids WHERE id = NM);
	
	IF IDINDEX = 0 AND IDTMPINDEX=0 THEN 
		INSERT INTO t_ids (id) VALUES(NM);
		SET RW = RW - 1;
	END IF;
END WHILE;

END outer_label; 

INSERT INTO CDKeycode (`id`,`sort`,`name`,`giftId`,`accountActive`,`roleIdActive`,`type`,`count`,`begintime`,`endtime`,`status`,`code`) SELECT `id`,`sort`,`name`,`giftId`,0,0,0,0,`begintime`,`endtime`,0,CONCAT(sort,id) FROM t_ids;
SELECT CONCAT(sort,convert(id,CHAR)) FROM t_ids;
END
;;
DELIMITER ;