MySQL：存储过程拆分列（带分隔符）并插入新表

2 回答

• 3

  看看RolandoMySQLDBA的帖子到这个dba.stackexchange question我觉得我在关于触发存储过程的初始保留中得到了证实 . 但是，如果您确定在任何给定时间仅通过用户输入更改了几行，则应该可以将快速操作的过程组合在一起 .

  但是，如果有许多用户并行工作，他们可能仍会互相锁定 . 我不知道这是否真的会发生，因为存储过程不会改变 details 表中的任何内容 . 如果有必要，你可以查看this page的想法 .

  Edit: TRIGGER

  我刚刚将上一篇文章的SQLfiddle扩展为SQLfiddle with trigger，包含以下内容：

  CREATE TRIGGER normdet AFTER INSERT ON detail FOR EACH ROW
  BEGIN
    DECLARE n int; DECLARE word VARCHAR(64)
  
   ;SET n=cntparts(NEW.inventor)
   ;WHILE n>0 DO
     SET word=part(new.inventor,n)
     ;IF NOT EXISTS (SELECT * FROM inv WHERE invname=word) THEN
       INSERT INTO inv (invname) VALUES (word)
     ;END IF
     ;INSERT INTO det2inv (didid,diiid) 
      SELECT NEW.id,invid FROM inv WHERE invname=word
     ;SET n=n-1
   ;END WHILE
    -- and similar loops for assignee and cls ...
  ;END;
  

  我还定义了另一个功能

  CREATE FUNCTION cntparts (var varchar(1024)) RETURNS int
  RETURN 1+LENGTH(var)-LENGTH(REPLACE(var,'|',''));
  

  计算给定 varchar 中的单词 . 这也可用于在我的第一篇文章中为基本转换创建循环而不是我固定的 UNION 构造 .

  触发器现在负责所有新的 INSERT . 仍然需要编写类似的触发器来为 UPDATE 做同样的事情 . 这不应该太难......

  在我的SQLfiddle中，我在触发器定义之后将另一行插入 detail . 结果由两个比较SELECT语句列出，请参见fiddle .

  reply to last comment ：

  好吧，正如我在原始答案中建议的那样，你应该首先导入所有数据（没有安装任何触发器!!!!），然后使用 SELECT/UNION 语句浏览 detail -table . 在您这样做之前，您应该通过使用找出每个列 assignee ， inventor 和 ipsubclass 中的最大单词数

  SELECT MAX(cntparts(inventor)) invcnt,
         MAX(cntparts(assignee)) asscnt,
         MAX(cntparts(ipsubclass)) clscnt 
  FROM detail
  

  然后，您可以调整每列所需的 SELECT/UNION 个数量 . 然后填写链接表，如SQLfiddle中所示 .

  也许整个过程需要一段时间，但您可以安全地在一个接一个的表上工作（首先是实际的属性表，然后是相关的链接表） .

  之后，您可以激活触发器，然后应该只对单独添加的行进行操作 .

  回复于 2024-05-05T21:58:14+08:00
• 2

  首先，在我看来，你应该将你的表分成四个独立的表：

  • detail （主表，包含： id, projectid, publicationnumber, prioritycountry, prioritydate 和 status ）

  • inv （发明人表，包含： invid, invname ，可能还有更多与发明人有关的数据）

  • cls （ipsubclass表，包含： clsid, clsname ，可能还有每个类的描述）

  • assignee （包含受让人公司的数据，如： assid, assname ... ）

  由于主表与 inv, cls 和 assignee 之间将存在 n:m 关系，因此您还应设置包含关系的链接表

  • det2inv

  • det2cls

  • det2ass

  重组任务可以分为几个步骤：

  首先，您需要应用用户定义的函数来拆分组合值 . 您可以使用here描述的功能执行此操作

  我进一步简化了它，因为在你的例子中我们只遇到一个单独的分隔符 | ：

  CREATE FUNCTION part( x VARCHAR(255), pos INT) 
  RETURNS VARCHAR(255) BEGIN
   DECLARE delim char(1)
   ;SET delim='|'
   ;RETURN TRIM(REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
                     LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
         delim, ''))
   ;END;
  

  （注意 TRIM 功能可以砍掉任何东西不必要的空白...）

  接下来，您应该定义包含您的发明者和可能的ipsubclasses的目标表（......和受让人，我还没有完成）：

  CREATE TABLE inv (invid int auto_increment PRIMARY KEY, invname nvarchar(64));
  CREATE TABLE cls (clsid int auto_increment PRIMARY KEY, clsname nvarchar(6));
  

  您可以根据需要随意扩展带有其他列的表格 .

  现在我们用唯一值填充表格 . 首先是表_1754820中的发明者：

  INSERT INTO inv (invname) 
  SELECT inv FROM (
   SELECT part(inventor,1) inv from detail
   UNION 
   SELECT part(inventor,2) from detail
   UNION 
   SELECT part(inventor,3) from detail
   UNION 
   SELECT part(inventor,4) from detail
   UNION 
   SELECT part(inventor,5) from detail
   UNION 
   SELECT part(inventor,6) from detail
   UNION 
   SELECT part(inventor,7) from detail
   UNION 
   SELECT part(inventor,8) from detail
  ) t WHERE inv>'' ORDER BY inv;
  

  接下来是ipsubclasses：

  INSERT INTO cls (clsname)
  SELECT icls FROM (
   SELECT part(iclass,1) icls from detail
   UNION 
   SELECT part(iclass,2) from detail
   UNION 
   SELECT part(iclass,3) from detail
   UNION 
   SELECT part(iclass,4) from detail
   UNION 
   SELECT part(iclass,5) from detail
   UNION 
   SELECT part(iclass,6) from detail
   UNION 
   SELECT part(iclass,7) from detail
   UNION 
   SELECT part(iclass,8) from detail
  ) t WHERE icls>'' ORDER BY icls;
  

  在我的例子中，我只查看了每个字段的前8个条目 . 这可以根据您的需要进行修改 . 您将得到两个唯一编号的表，其中包含所有可能的发明者和所有可能的子类（并且以类似的方式所有受让人） . 您可以在这里查看我的SQLfiddle：http://sqlfiddle.com/#!2/aeafe/1

  剩下的任务现在是用合适的键填充链接表（主表 details 及其属性表 inv, cls 和 assignee 中的id对 .

  Edit

  链接表填充了以下语句：

  INSERT INTO det2inv (didid,diiid)
  SELECT id,invid FROM inv 
  INNER JOIN detail ON INSTR(inventor,invname)>0;
  
  INSERT INTO det2cls (dcdid,dccid)
  SELECT id,clsid FROM cls 
  INNER JOIN detail ON INSTR(iclass,clsname)>0;
  
  -- ... and a similar one for det2ass
  

  INSTR() 函数将无法正常工作，因为 Hagen, Pete 等名称将与 Gleichenhagen, Peter 成功匹配 . 为避免这些情况，应修改比较，如下所示：

  ...
  INNER JOIN detail ON INSTR(REPLACE(CONCAT('|',inventor,'|'),' ',''),
                             REPLACE(CONCAT('|',invname,'|'),' ',''))>0;
  

  你可以在这里看到 complete working example ：http://sqlfiddle.com/#!2/097be/8

  回复于 2024-05-05T21:58:14+08:00