使用MySQL编写带有事件的While循环

huangapple go评论78阅读模式
英文:

Writing a While Loops with Events in MySQL

问题

我想在MySQL中使用事件编写一个带有事件的While循环。在搜索互联网后,不幸的是,我没有找到类似的内容。然而,我不想使用存储过程来编写它。
CREATE DEFINER=`root`@`localhost` EVENT `try` 
ON SCHEDULE EVERY 1 DAY STARTS '2023-03-10 09:00:00.000000'
ON COMPLETION NOT PRESERVE ENABLE DO 
BEGIN
    DECLARE counter int DEFAULT 1;
    WHILE counter <= 10 DO
    INSERT INTO admin(admin.admin_username, admin.admin_password, admin.admintype_id) VALUES('aaaa','bbbb',1)
    SET counter = counter + 1;
    END WHILE
END;
英文:

I want to write a While Loop with Events in MySQL. After searching the internet, unfortunately, i didn't find something like this. However, i don't want to write it using procedures.

CREATE DEFINER=`root`@`localhost` EVENT `try` 
ON SCHEDULE EVERY 1 DAY STARTS &#39;2023-03-10 09:00:00.000000&#39;
ON COMPLETION NOT PRESERVE ENABLE DO 
BEGIN
    DECLARE counter int DEFAULT 1;
    WHILE counter &lt;= 10 DO
    INSERT INTO admin(admin.admin_username, admin.admin_password, admin.admintype_id) VALUES(&#39;aaaa&#39;,&#39;bbbb&#39;,1)
    SET counter = counter + 1;
    END WHILE
END;

答案1

得分: 1

除非这是一个在事件中使用while循环的练习,否则我认为可以通过使用INSERT INTO admintouser_cocuk(admintouser_cocuk.kullanici_cocuk_id, admintouser_cocuk.admintouser_cocuk_mesaj) SELECT kullanici_cocuk.kullanici_cocuk_id,'Sevgili evladınızın yeni yaşını kutlar, tüm ailenizle birlikte mutlu bir yaşam dileriz.' FROM kullanici_cocuk WHERE SUBSTRING(kullanici_cocuk.cocuk_dogum_tarihi, 6, 5) = SUBSTRING(CURDATE(), 6, 5) ; 这样也可以实现相同的效果。而且我怀疑使用日期函数而不是子字符串会更整洁。

英文:

Unless this is an exercise in using a while loop in an event I think the same can be achieved with and insert..select

INSERT INTO admintouser_cocuk(admintouser_cocuk.kullanici_cocuk_id, 
            admintouser_cocuk.admintouser_cocuk_mesaj)
SELECT kullanici_cocuk.kullanici_cocuk_id ,
       &#39;Sevgili evladınızın yeni yaşını kutlar, t&#252;m ailenizle birlikte mutlu bir yaşam dileriz.&#39;
FROM kullanici_cocuk 
wHERE SUBSTRING(kullanici_cocuk.cocuk_dogum_tarihi, 6, 5) = SUBSTRING(CURDATE(), 6, 5)
; 

And I suspect using a date functions rather than subtrings would be tidier too.

huangapple
  • 本文由 发表于 2023年3月9日 19:16:53
  • 转载请务必保留本文链接:https://go.coder-hub.com/75683858.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定