触发器无法识别表(通过触发器将NEW.values的内容分解为多行以插入到另一个表中) [英] Trigger does not recognize table (Trigger to break up content of NEW.values into multiple rows to insert into another table)

问题描述

错误代码:1109.字段列表中的未知表编号"

为什么我的代码认为没有表号以及如何解决它?

Why my code thinks that there is no table numbers and how to fix it?

如果可能的话，请回答问题为什么要在触发器中使用用例?

And if possible answer question why use case with triggers?

P.S Numbers表我一直在与sunstring_index结合使用，因此从某些列字段中有两个单词的表中，我可以将它们分为两个行

P.S Numbers table I have been using combining with sunstring_index so from tables where in some column fields have two words i could split them into two rows

也许有足够的方法?

drop schema exp;
create database exp;
use exp;
create table IDVU (
`ID` int(8)  unsigned not null auto_increment ,
`VU` varchar(45) not null,
PRIMARY KEY (`id`),
KEY `ix_VU` (`VU`)
)ENGINE = InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

create table sep (
ID1 int(8) unsigned NOT NULL primary key auto_increment,
ID2 int(8) unsigned not null,
V varchar(45) not null,
U varchar(45) not null,
KEY `ix_ID2` (`ID2`),
CONSTRAINT `ID_IDVU_SEP` FOREIGN KEY (`ID2`) REFERENCES `IDVU` (`ID`)
ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE = InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

create table numbers select 1 n union all select 2;
delimiter $$
CREATE TRIGGER `edit` AFTER INSERT 
ON `idvu` 
FOR EACH ROW BEGIN 

    IF new.VU like '% %' THEN 
        SET @V = SUBSTRING_INDEX(SUBSTRING_INDEX(new.Vu, ' ', numbers.n), ' ', -1),
            @U = SUBSTRING_INDEX(SUBSTRING_INDEX(new.Vu, ' ', numbers.n), ' ', -1);
    else
        SET @V = 'NEW',@U = 'NEW';
    END IF;

    INSERT INTO sep (ID2,V, U) VALUES (new.ID,@V, @U);

END$$

delimiter ;

select * from idvu order by ID;
select * from sep order by ID1;
insert into iDVU value (2,'Dd Rr');

更新:OP希望创建触发器AFTER INSERT，以将插入到table1中的NEW.values的内容分解为不同的行，并将其插入到table2中.

UPDATE: OP wants to create a trigger AFTER INSERT to break up content of NEW.values inserted into table1 into different rows and insert them into table2.

Table1
Number  Player    Team              Position
  1     Jan Ho    Team 1            C
  2     Mike Dog  Team 3            LW
4 8     Slim Dre  Team 4, Team 1    G D
  6     Mad Dog   Team 2            D

将其分成几行，然后像下面那样插入table2

break it up into rows and insert into table2 like below

Table2
Number  Player    Team              Position
  1     Jan Ho    Team 1            C
  2     Mike Dog  Team 3            LW
  4     Slim Dre  Team 4            G
  8     Slim Dre  Team 1            D
  6     Mad Dog   Team 2            D

推荐答案

如果您只是尝试分解字符串，则可以像这样对其中的1和2进行硬编码，而无需获取1和2在数字表中，因为该表目前已被硬编码为包含1和2.

if you're just trying to break out the strings, you can just hardcode the 1 and 2 in there like this and there's no need to grab 1 and 2 in numbers table since that table is currently hardcoded to contain 1 and 2 anyways.

SET @V = SUBSTRING_INDEX(SUBSTRING_INDEX(new.Vu, ' ', 1), ' ', -1),
    @U = SUBSTRING_INDEX(SUBSTRING_INDEX(new.Vu, ' ', 2), ' ', -1);

sqlfiddle

但是后来我注意到您甚至不需要两次调用SUBSTRING_INDEX().这也可以

but then i noticed you don't even need to call SUBSTRING_INDEX() twice..this works too

    SET @V = SUBSTRING_INDEX(new.Vu, ' ', 1),
        @U = SUBSTRING_INDEX(new.Vu,' ', -1);

sqlfiddle

更新，看到您的评论后，我明白了您为什么要创建表numbers的原因，因此您的触发器将是这样的. 首先，您创建表numbers，其中包含具有n值(从1到10)的最大行(分解为行的最大字段数). 然后，从numbers中选择，其中n个值是< =您的数字中的字段数.然后应用SUBSTRING_INDEX()函数以使该字段位于n位置.

UPDATE after seeing your comment, I see why you wanted to create table numbers so your trigger would be something like this. First you create table numbers that contains rows that has n values from 1 to 10 (possible maximum number of fields to break up into rows). Then you select from numbers where n values are <= number of fields in your number. Then apply SUBSTRING_INDEX() functions to get the field at n position.

create table numbers 
select 1 as n 
union select 2 
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10;

CREATE TRIGGER `edit2` AFTER INSERT 
ON `table1` 
FOR EACH ROW BEGIN 

    INSERT INTO table2 (number,player,team,position)
    SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.number,' ',n),' ',-1) as number,
    NEW.player as player,
    SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.team,', ',n),', ',-1) as team,
    SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.position,' ',n),' ',-1) as position
    FROM
    numbers n
    WHERE LENGTH(NEW.number)
             - LENGTH(REPLACE(NEW.number,' ',''))
             + 1 >= n.n;

END

sqlfiddle可以看到触发作用

这篇关于触发器无法识别表(通过触发器将NEW.values的内容分解为多行以插入到另一个表中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！