/**
此文件存放 特殊sql案例模板
**/


/**查询随机号码sql**/

SELECT * FROM yyg_goods_purchase_no WHERE record_id=0 AND stage_id=1 ORDER BY RAND() LIMIT 1  ;  


/**

新增子表模板

CREATE TABLE `yyg_goods_purchase_record_1` (
  `id` VARCHAR(26) NOT NULL COMMENT '主键，按规则生成',
  `uid` BIGINT(20) NOT NULL COMMENT '用户表主键',
  `stage_id` BIGINT(20) NOT NULL COMMENT '商品期数表主键',
  `stage_num` INT(8) DEFAULT NULL COMMENT '当前期数',
  `uname` VARCHAR(40) DEFAULT NULL COMMENT '用户名称',
  `all_price` INT(8) DEFAULT NULL COMMENT '参与人次数',
  `ip` VARCHAR(26) DEFAULT NULL COMMENT 'ip',
  `createdate` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  KEY `stage_id` (`stage_id`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='一元购参与记录表';

重建merge表

DROP TABLE IF EXISTS `yyg_goods_type`;

CREATE TABLE `yyg_goods_type` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(18) DEFAULT NULL,
  `fatherid` int(4) DEFAULT '0' COMMENT '一级值为0，二级值为对应的一级主键id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COMMENT='商品类型表';

/*Data for the table `yyg_goods_type` */




DROP TABLE IF EXISTS `yyg_user_translog`;

CREATE TABLE `yyg_user_translog` (
  `id` varchar(26) NOT NULL,
  `uid` bigint(20) NOT NULL COMMENT '用户表主键',
  `meif_balance` int(10) DEFAULT NULL COMMENT '美分交易余额',
  `trastype` int(2) DEFAULT '1' COMMENT '1=充值 2=消费 3=奖励',
  `acc_balance` int(10) DEFAULT NULL COMMENT '账户余额',
  `meif_trans_price` int(10) DEFAULT NULL COMMENT '美分交易金额',
  `acc_trans_price` bigint(10) DEFAULT '0' COMMENT '账户交易金额',
  `descript` varchar(40) DEFAULT NULL COMMENT '交易描述',
  `createdate` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  KEY `trastype` (`trastype`),
  KEY `createdate` (`createdate`)
) ENGINE=MERGE CHARSET=utf8 COMMENT='用户交易日志表' UNION=(yyg_user_translog_1) INSERT_METHOD=LAST;




insert  into `yyg_goods_type`(`id`,`name`,`fatherid`) values (1,'手机数码',0),(2,'电脑办公',0),(3,'家用电器',0),(4,'黄金首饰',0),(5,'日用个护',0),(6,'其他商品',0),(8,'手机',1),(9,'数码影音',1),(10,'虚拟物品',1),(11,'平板电脑',2),(12,'笔记本',2),(13,'台式机',2),(14,'大家电',3),(15,'电视',3),(16,'空调',3),(17,'小家电',3),(18,'实物黄金',4),(19,'钟表首饰',4),(20,'化妆个护',5),(21,'母婴',5),(22,'日用品',5),(23,'汽车',6),(24,'电车',6),(25,'户外运动',6);


DROP TABLE yyg_goods_purchase_record;
CREATE TABLE `yyg_goods_purchase_record` (
  `id` VARCHAR(26) NOT NULL COMMENT '主键，按规则生成',
  `uid` BIGINT(20) NOT NULL COMMENT '用户表主键',
  `stage_id` BIGINT(20) NOT NULL COMMENT '商品期数表主键',
  `stage_num` INT(8) DEFAULT NULL COMMENT '当前期数',
  `uname` VARCHAR(40) DEFAULT NULL COMMENT '用户名称',
  `all_price` INT(8) DEFAULT NULL COMMENT '参与人次数',
  `ip` VARCHAR(26) DEFAULT NULL COMMENT 'ip',
  `createdate` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  KEY `stage_id` (`stage_id`)
) ENGINE=MERGE CHARSET=utf8 COMMENT='一元购参与记录表' UNION=(yyg_goods_purchase_record_1) INSERT_METHOD=LAST;


动态增加merge子表 模板
ALTER TABLE yyg_goods_purchase_record  UNION=(yyg_goods_purchase_record_1,yyg_goods_purchase_record_2,yyg_goods_purchase_record_3)

**/