优化所用的表

DROP TABLE IF EXISTS `tb_sequence`;

CREATE TABLE `tb_sequence` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


insert  into `tb_sequence`(`id`) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);


DROP TABLE IF EXISTS `user1`;

CREATE TABLE `user1` (
  `id` int(11) NOT NULL,
  `user_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `over` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `money` decimal(15,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert  into `user1`(`id`,`user_name`,`over`,`mobile`,`money`) values (1,'唐僧','旃檀功德佛','13812345678,13811112222,13822223333','35000.00'),(2,'猪八戒','净坛使者','13711112222,13722223333','15000.00'),(3,'孙悟空','齐天大圣','13611111111,13622222222,13633333333,13644444444','28000.00'),(4,'沙僧','金身罗汉','13511111111,13522222222','8000.00');



DROP TABLE IF EXISTS `user2`;

CREATE TABLE `user2` (
  `id` int(11) NOT NULL,
  `user_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `over` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


insert  into `user2`(`id`,`user_name`,`over`) values (1,'孙悟空','成佛'),(2,'牛魔王','被降伏'),(3,'蛟魔王','被降伏'),(4,'鹏魔王','被降伏'),(5,'狮驼王','被降伏');


DROP TABLE IF EXISTS `user_kills`;

CREATE TABLE `user_kills` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `killday` date DEFAULT NULL,
  `kills` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


insert  into `user_kills`(`id`,`user_id`,`killday`,`kills`) values (1,2,'2015-01-10',10),(2,2,'2015-02-01',2),(3,2,'2015-02-05',12),(4,4,'2015-01-10',3),(5,4,'2015-02-11',5),(6,4,'2015-02-06',1),(7,3,'2015-01-11',20),(8,3,'2015-02-12',10),(9,3,'2015-02-07',17);

DROP TABLE IF EXISTS `user1_equipment`;

CREATE TABLE `user1_equipment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `arms` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `clothing` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shoe` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert  into `user1_equipment`(`id`,`user_id`,`arms`,`clothing`,`shoe`) values (1,3,'金箍棒','锁子黄金甲','藕丝步云履'),(2,2,'九齿钉耙','僧衣','僧鞋'),(3,4,'降妖宝杖','僧衣','僧鞋'),(4,1,'九环锡杖','锦斓袈裟','僧鞋');


DROP TABLE IF EXISTS `order_seq`;

CREATE TABLE `order_seq` (
  `timestr` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `order_sn` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `taxrate`;

CREATE TABLE `taxrate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `low` decimal(15,2) DEFAULT NULL,
  `high` decimal(15,2) DEFAULT NULL,
  `rate` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

 insert  into `taxrate`(`id`,`low`,`high`,`rate`) values (1,'0.00','1500.00','0.03'),(2,'1500.00','4500.00','0.10'),(3,'4500.00','9000.00','0.20'),(4,'9000.00','35000.00','0.25'),(5,'35000.00','55000.00','0.30'),(6,'55000.00','80000.00','0.35'),(7,'80000.00','999999999.00','0.45');



DELIMITER $$

CREATE
    PROCEDURE seq_no()
    BEGIN
	DECLARE v_cnt INT;
	DECLARE v_timestr VARCHAR(8);
	DECLARE rowcount BIGINT;
	SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d');
	SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;
	START TRANSACTION;
		UPDATE order_seq SET order_sn = order_sn + v_cnt WHERE timestr=v_timestr;
		IF ROW_COUNT() = 0 THEN
			INSERT INTO order_seq(timestr,order_sn) VALUES (v_timestr,v_cnt);
		END IF;
		SELECT CONCAT(v_timestr,LPAD(order_sn,7,0)) AS order_sn FROM order_seq WHERE timestr = v_timestr;
	COMMIT;	
    END$$

DELIMITER ;