如何使用sql代码中的函数'begin原子‘来创建触发器？

Question

我的代码

 delimiter //
 create table NumAppApk as select count(distinct App_Name) as AppNum, count(ID) as ApkNum 
 from market_app_metadata;

 create trigger numCounter before insert on market_app_metadata
 for each row
 begin
     set @tmp = (select count(*) from market_app_metadata where App_Name = new.App_Name);
     update NumAppApk set ApkNum = ApkNum + 1;
     if @tmp < 1 then
         update NumAppApk set AppNum = AppNum + 1;
     end if;
 end;
 //

一些定义和目的

1. market_app_metadata 该表包括列：App_Name (表示应用程序的真实名称)、Package_Name (表示apk包的名称)。因此，相同apk的不同版本有不同的apk。
2. NumAppApk 因此，这个表被设置为计算market_app_metadata表中有多少应用程序和apks。但是由于表一直在更新，所以我想使用mysql trigger快速查询表NumAppApk中的实时数据。

难点

若要计算计数的App_Name，每次出现新行时，我将使用

set @tmp = (select count(*) from market_app_metadata where App_Name = new.App_Name);

要计算表中有多少应用程序与这个新应用程序具有相同的名称。

因此，如果是@tmp == 0，这意味着新行代表了一个新的应用程序。

我找不到我的方法的逻辑错误，但是结果是错误的

​

​

结果发现，条件语句if @tmp < 1 then不起作用，或者我认为我应该使用begin atomic来确保原子性。

给出结论-- 的两个问题

• 我的代码中有逻辑错误吗？
• 如何在begin atomic服务器5.7.18中使用MySQL？(此语句将导致错误mysql - ERROR 1064 (42000): You have an error in your SQL syntax ...。)

Answer 1

我不认为你的逻辑是错误的，或者代码是错误的，这就是为什么

MariaDB [sandbox]> /*
   /*> DROP TABLE IF EXISTS market_app_metadata;
   /*> CREATE TABLE market_app_metadata( id INT AUTO_INCREMENT PRIMARY KEY, APP_NAME VARCHAR(10));
   /*> create table numappapk (id int default 1,appnum int,apknum int);
   /*> */
MariaDB [sandbox]> TRUNCATE TABLE OCCURSRESULTS;
Query OK, 0 rows affected (0.18 sec)

MariaDB [sandbox]> truncate table market_app_metadata;
Query OK, 0 rows affected (0.20 sec)

MariaDB [sandbox]> drop table if exists numappapk;
Query OK, 0 rows affected (0.10 sec)

MariaDB [sandbox]>
MariaDB [sandbox]>
MariaDB [sandbox]> INSERT INTO market_app_metadata(APP_NAME) VALUES ('AAA'),('BBB'),('AAA');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [sandbox]> create table NumAppApk as select count(distinct App_Name) as AppNum, count(ID) as ApkNum
    ->  from market_app_metadata;
Query OK, 1 row affected (0.28 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> SELECT * FROM NUMAPPAPK;
+--------+--------+
| AppNum | ApkNum |
+--------+--------+
|      2 |      3 |
+--------+--------+
1 row in set (0.00 sec)

Numappapk和我预期的一样。

如果我们创建触发器

 DROP TRIGGER IF EXISTS numcounter;
 delimiter //
 #create table NumAppApk as select count(distinct App_Name) as AppNum, count(ID) as ApkNum 
 #from market_app_metadata;

 create trigger numcounter before insert on market_app_metadata
 for each row
 begin
    insert into occursresults(msg) values ('start');
     #set tmp = IFNULL((select count(*) from market_app_metadata where App_Name = new.App_Name),0);
     set @tmp = (select count(*) from market_app_metadata where App_Name = new.App_Name);
     insert into occursresults(msg) values (concat('tmp:', ifnull(@tmp,'null'),' newappname:', ifnull(new.app_name,'newapp is null')));
     #select appnum,apknum into _appnum,_apknum from NumAppApk;

     set @appnum = (select appnum from NumAppApk);
     set @apknum = (select apknum from NumAppApk);
      insert into occursresults(msg) values (concat(' before update apknum appnum:',ifnull(@appnum,0),' apknum:',ifnull(@apknum,0)));
     update NumAppApk set ApkNum = ApkNum + 1;
     if @tmp < 1 then
            insert into occursresults(msg) values (concat(ifnull(new.app_name,'newapp is null'), ' tmp < 1'));
         update NumAppApk set AppNum = AppNum + 1;
     end if;
 end  //

 DELIMITER ;

注意，出现的结果表是用于调试的。

然后我们做这个

INSERT INTO market_app_metadata(APP_NAME) VALUES ('CCC'),('AAA');
select * from market_app_metadata;
select count(distinct app_name) from market_app_metadata;
SELECT * FROM NUMAPPAPK;
select * from occursresults;

我们明白了

MariaDB [sandbox]> select * from market_app_metadata;
+----+----------+
| ID | APP_NAME |
+----+----------+
|  1 | AAA      |
|  2 | BBB      |
|  3 | AAA      |
|  4 | CCC      |
|  5 | AAA      |
+----+----------+
5 rows in set (0.00 sec)

MariaDB [sandbox]> select count(distinct app_name) from market_app_metadata;
+--------------------------+
| count(distinct app_name) |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)

MariaDB [sandbox]> SELECT * FROM NUMAPPAPK;
+--------+--------+
| AppNum | ApkNum |
+--------+--------+
|      3 |      5 |
+--------+--------+
1 row in set (0.00 sec)

MariaDB [sandbox]> select * from occursresults;
+----+-----------------------------------------+
| id | msg                                     |
+----+-----------------------------------------+
|  1 | start                                   |
|  2 | tmp:0 newappname:CCC                    |
|  3 |  before update apknum appnum:2 apknum:3 |
|  4 | CCC tmp < 1                             |
|  5 | start                                   |
|  6 | tmp:2 newappname:AAA                    |
|  7 |  before update apknum appnum:3 apknum:4 |
+----+-----------------------------------------+
7 rows in set (0.00 sec)

Numappapk和我预期的一样，结果显示，如果tmp <1代码正在执行的'CCC‘。

如果我使用的模型与您的不一致，或者最终的结果不像您期望的那样，请看一看并评论一下。