主页 / user-270026

Cromega08's questions

Martin Hope
Cromega08
Asked: 2023-03-04 08:21:57 +0800 CST
  • 7

请阅读所有内容，包括最后的注释和编辑

问题

我有一个带有 3 个不同表的 MySQL 数据库：

  1. 为了节省用户
mysql> describe users;
+-------------+-----------------------------------+------+-----+---------+----------------+
| Field       | Type                              | Null | Key | Default | Extra          |
+-------------+-----------------------------------+------+-----+---------+----------------+
| id          | int                               | NO   | PRI | NULL    | auto_increment |
| firstname   | varchar(255)                      | NO   |     | NULL    |                |
| lastname    | varchar(255)                      | YES  |     | NULL    |                |
| username    | varchar(255)                      | NO   | UNI | NULL    |                |
| pswd        | varchar(255)                      | NO   |     | NULL    |                |
| permissions | enum('admin', 'student')          | NO   |     | NULL    |                |
| active      | tinyint(1)                        | NO   |     | 1       |                |
+-------------+-----------------------------------+------+-----+---------+----------------+
7 rows in set (0,01 sec)
  1. 为了救学生

mysql> describe students;
+--------------+--------------+------+-----+--------------+-------------------+
| Field        | Type         | Null | Key | Default      | Extra             |
+--------------+--------------+------+-----+--------------+-------------------+
| id           | int          | NO   | PRI | NULL         |                   |
| firstname    | varchar(255) | NO   |     | not assigned |                   |
| lastname     | varchar(255) | YES  |     | NULL         |                   |
| id_number    | int          | NO   | UNI | -1           |                   |
| registration | date         | NO   |     | curdate()    | DEFAULT_GENERATED |
+--------------+--------------+------+-----+--------------+-------------------+
5 rows in set (0,01 sec)
  1. 拯救管理员
mysql> describe admins;
+--------------+--------------+------+-----+--------------+-------------------+
| Field        | Type         | Null | Key | Default      | Extra             |
+--------------+--------------+------+-----+--------------+-------------------+
| id           | int          | NO   | PRI | NULL         |                   |
| firstname    | varchar(255) | NO   |     | not assigned |                   |
| lastname     | varchar(255) | YES  |     | NULL         |                   |
| registration | date         | NO   |     | curdate()    | DEFAULT_GENERATED |
+--------------+--------------+------+-----+--------------+-------------------+
4 rows in set (0,00 sec)

我想为用户创建一个插入后触发器，以根据字段users.permissions在其他触发器中插入一行。

  • 扳机：
delimiter $$
create trigger users_ai
after insert
on users for each row
begin
    set @id_to_update := new.id;
    set @table_to_update := (select permissions
                            from users
                            where [email protected]_to_update
                        );  
    set @query := concat(
        'update ',
        quote(@table_to_update),
        's set firstname=(select firstname from users where id=',
        quote(@id_to_update),
        '),lastname=(select lastname from users where id=',
        quote(@id_to_update),
        ') where id=',
        quote(@id_to_update)
    );
    exec @query;
end;
$$
delimiter ;

但是，总是出现以下错误：

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

Error position: line: 4

DBeaver（我用来处理 DB 的编辑器）抛出了以下细节：

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:582)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:491)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:498)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:920)
    at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3805)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5068)
    at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
    at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:96)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
    ... 12 more

我试过的

  1. 检查语法、空格或奇怪字符等基本内容。
  2. 在触发器中设置 @id_to_update变量的不同形式：
    • 新的.id
    • last_insert_id()
    • StackOverflow 上的这段代码： 
      set @id_to_update = (select id from users order by id desc limit 1);
if @id_to_update is null or @id_to_update = '' then set @id_to_update = 1;

笔记

  1. 我对数据库的了解是基础知识。
  2. 这只是我在空闲时间制作的“玩具项目”的一部分，只是为了学习新事物。
  3. MySQL版本： 
    ~ mysql --version
mysql  Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

编辑

根据@Ergest Basha 的回答和其他建议，将所有内容替换为条件，对表格进行一些更改并尝试使所有内容更简单，现在看起来像：

  1. 用户：
mysql> describe users;
+-------------+-----------------------------------+------+-----+---------+----------------+
| Field       | Type                              | Null | Key | Default | Extra          |
+-------------+-----------------------------------+------+-----+---------+----------------+
| id          | int                               | NO   | PRI | NULL    | auto_increment |
| firstname   | varchar(255)                      | NO   |     | NULL    |                |
| lastname    | varchar(255)                      | YES  |     | NULL    |                |
| username    | varchar(255)                      | NO   | UNI | NULL    |                |
| pswd        | varchar(255)                      | NO   |     | NULL    |                |
| permissions | enum('admin','student')           | NO   |     | NULL    |                |
| active      | tinyint(1)                        | NO   |     | 1       |                |
+-------------+-----------------------------------+------+-----+---------+----------------+
7 rows in set (0,01 sec)
  1. 学生：
mysql> describe students;
+--------------+--------------+------+-----+-----------+-------------------+
| Field        | Type         | Null | Key | Default   | Extra             |
+--------------+--------------+------+-----+-----------+-------------------+
| id           | int          | NO   | PRI | NULL      |                   |
| firstname    | varchar(255) | NO   |     | NULL      |                   |
| lastname     | varchar(255) | YES  |     | NULL      |                   |
| id_number    | int          | NO   | UNI | -1        |                   |
| registration | date         | NO   |     | curdate() | DEFAULT_GENERATED |
+--------------+--------------+------+-----+-----------+-------------------+
5 rows in set (0,00 sec)
  1. 管理员：
mysql> describe admins;
+--------------+--------------+------+-----+-----------+-------------------+
| Field        | Type         | Null | Key | Default   | Extra             |
+--------------+--------------+------+-----+-----------+-------------------+
| id           | int          | NO   | PRI | NULL      |                   |
| firstname    | varchar(255) | NO   |     | NULL      |                   |
| lastname     | varchar(255) | YES  |     | NULL      |                   |
| registration | date         | NO   |     | curdate() | DEFAULT_GENERATED |
+--------------+--------------+------+-----+-----------+-------------------+
4 rows in set (0,00 sec)
  1. 用户表触发器：
delimiter $$
create trigger users_ai
after insert
on users for each row
begin
    case new.permissions
        when 'student' then
            insert into students (id, fistname, lastname)
            values (new.id, new.firstname, new.lastname);
        else
            insert into admins (id, firstname, lastname)
            values (new.id, new.firstname, new.lastname);
    end case;
end
$$
delimiter ;

但仍然无法正常工作，并在begin语句后继续出现相同的错误：

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8

此时不知道是否只是我机器上的错误，衷心感谢所有建议。

mysql