我的MySQL数据库包含两个表:user
和coupon
(一对一关系)。
我想选择所有没有优惠券的用户,然后创建一个新的(优惠券和唯一优惠券)。
user TABLE:
___________________________________
| id | name | coupon_id |
-----------------------------------
1 John 5
2 Mary (null) // Need to create one coupon.
3 Doe 2
4 Max (null) // Need to create one coupon.
5 Rex 1
7 Bill (null) // Need to create one coupon.
coupon TABLE:
______________________________________________
| id | code (random 6-chars - unique) |
----------------------------------------------
1 80k2ni
2 0akdne
5 nk03jd
捷径:
选择所有没有优惠券的用户: SELECT * from user WHERE coupon_id IS NULL;
随机生成一个6个字符的字符串(MySQL的): LEFT(sha1(rand()), 6)
。
假设您不介意从下一个继续从6继续coupon_id
,可以按以下步骤进行操作(请参见SQL Fiddle Demo):
-- Declare and set variables
SET @id_for_insert = (SELECT MAX(`id`) FROM `coupon`);
SET @id_for_update = @id_for_insert;
-- Insert new coupons
INSERT INTO `coupon` (id, code)
SELECT @id_for_insert := @id_for_insert + 1, LEFT(SHA1(RAND()), 6)
FROM `user`
WHERE coupon_id IS NULL;
-- Update users that don't already have a coupon with the newly created coupons
UPDATE `user`
SET coupon_id = @id_for_update := @id_for_update + 1
WHERE coupon_id IS NULL;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句