场景：

今天生成礼券，里面一个字段card_pwd 字段，是用户充值的密码（有唯一键约束），通过输入密码可以直接充值，因为大量生成卡券会导致密码重复率很高，mysql默认对字段大小写不敏感，这里要求这个字段对大小写敏感，其他字段不受影响。

生成的语句虽然有差别，效果是等同的，

修改排序规则的语句为：

ALTER TABLE `fddb`.`fd_voucher` 

MODIFY COLUMN `card_val` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '0' COMMENT '礼券值' AFTER `card_n`;

选中使用“二进制的语句”

ALTER TABLE `fddb`.`fd_voucher` 

MODIFY COLUMN `card_val` varchar(20) BINARY CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '礼券值' AFTER `card_n`;

但是效果是等同的

生成SQL如下：

/* 函数:rand_string*/

DELIMITER //

DROP FUNCTION IF EXISTS rand_string;

CREATE FUNCTION rand_string(n INT) RETURNS varchar(255) CHARSET utf8

BEGIN

DECLARE chars_str varchar(255) DEFAULT

'abcdefghijkmnpqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ23456789';

DECLARE return_str varchar(255) DEFAULT '';

DECLARE i INT DEFAULT 0;

WHILE i < n DO

SET return_str = concat(return_str,substring(chars_str,FLOOR(1 + RAND()*LENGTH(chars_str)),1));

SET i = i + 1;

END WHILE;

RETURN return_str;

END//

-- -----------------------------------------------------------------------------------------------------------

/* 函数:rand_num*/

DELIMITER //

DROP FUNCTION IF EXISTS rand_num ;

CREATE FUNCTION rand_num(n INT,chars_str VARCHAR(32)) RETURNS varchar(32) CHARSET utf8

BEGIN

DECLARE return_str varchar(32) DEFAULT '';

DECLARE i INT DEFAULT 0;

WHILE i < n DO

SET return_str := concat(return_str,substring(chars_str , FLOOR(1 + RAND()*LENGTH(chars_str)),1));

SET i := i + 1;

END WHILE;

RETURN return_str;

END//

-- ---------------------------------------------------------------------------------------------------------------------------

/*第一次生成可以使用*/

use fddb;

set @a=100000000;

INSERT INTO `fddb`.`fd_voucher` ( `card_sn`, `card_n`, `card_val`, `card_pwd`, `create_time`, `expire_time`, `batch_no` )

select concat('SN',@a := 1+@a) as 'card_sn',

concat((select rand_num(3,'456789')),

CEILING(RAND()*500000+500000),

(select rand_num(4,'0123456789')

),DATE_FORMAT(now(),'%S')) as card_n,100 as card_val ,(select rand_string(6) from dual ) as card_pwd ,now() as create_time,DATE_ADD(now(),INTERVAL 1 year) as expire_time,10000 as batch_no from fddb.fd_member_shell_log limit 100000;

/*第一次之后的生成使用*/

use fddb;

set @max_card_sn :=(select replace(max(card_sn),'SN','') from fddb.fd_voucher);

set @gen_batch_no=10000003;

 INSERT INTO `fddb`.`fd_voucher` ( `card_sn`, `card_n`, `card_val`, `card_pwd`, `create_time`, `expire_time`, `batch_no` )

select concat('SN',@max_card_sn := 1+@max_card_sn) as 'card_sn',

concat((select rand_num(3,'456789')),

CEILING(RAND()*500000+500000),

(select rand_num(4,'0123456789')

),DATE_FORMAT(now(),'%S')) as card_n,10 as card_val ,(select rand_string(6) from dual ) as card_pwd ,now() as create_time,DATE_ADD(now(),INTERVAL 1 year) as expire_time, @gen_batch_no as batch_no from fddb.fd_member_shell_log limit 1000;