时间戳

首页 > 解决方案 > 在 INSERT > SELECT 中使用带有 CONCAT_WS 的变量作为表名

mysql - 在 INSERT > SELECT 中使用带有 CONCAT_WS 的变量作为表名

问题描述

我有一个存储各种仪器读数的数据库。默认情况下，每个月都会在数据库中创建一个新表，并将仪器读数插入该月的表中。例如，2021 年 1 月的表称为 data_1_2021_01。2021 年 2 月的表称为 data_1_2021_02。

以下存储过程创建一个表并从该月份的表（在示例中为 data_1_2021_01）和包含仪器标签信息的第二个表中插入特定月份的数据。该过程（如下所示）按预期返回我需要的结果：

DELIMITER //
    
    CREATE
    
        PROCEDURE db.DATAPREP ()   
    
    BEGIN
    
    DROP TABLE IF EXISTS db.DATA;
    
    CREATE TABLE db.DATA (
        ts DATETIME,
        tagid INT(11) NOT NULL,
        tagpath VARCHAR(255),
        curvalue FLOAT,
        t_stamp BIGINT(20) NOT NULL,
        INDEX (tagid , tagpath)
    );  
    
    INSERT INTO db.DATA (
            ts,
            tagid,
            tagpath,
            curvalue,
            t_stamp) 
    
    SELECT 
            FROM_UNIXTIME(data_1_2021_01.t_stamp/1000), 
            data_1_2021_01.tagid, 
            te.tagpath, 
            Case WHEN data_1_2021_01.FloatValue IS Null then data_1_2021_01.intValue
                else data_1_2021_01.floatvalue END,
            data_1_2021_01.t_stamp
            FROM data_1_2021_01, te
             where data_1_2021_01.tagid=te.id; 
     
    END //
    DELIMITER ;

现在我需要自动化这个过程，这样存储过程可以按月执行，并从当月的表中提取数据，而无需手动更新过程中的表名。我已经声明了变量来获取表名，但我无法让 INSERT INTO > SELECT 正常工作。如果我使用 CONCAT_WS 来识别 SELECT 语句中的表/列名称，它会尝试将结果存储为数据值，而不是将其视为要从中提取数据值的表/列。我尝试过使用 PREPARE、EXECUTE、DEALLOCATE，但我似乎也无法让它发挥作用。我只是对如何在 MySQL 中进行这项工作还不够熟悉，因此非常感谢任何帮助。这是脚本（减去 PREPARE、EXECUTE、DEALLOCATE 语句）：

DELIMITER //
CREATE

    PROCEDURE db.DATAPREP ()   

BEGIN

              -- Set this month's table.  
       DECLARE CurrentMthTable varchar(16383);
       
            -- Set the current year.  
       DECLARE CurrentYear char(4); 
        
               -- Set the current month.
       DECLARE CurrentMth varchar(2);
       
               -- GET the year for the current month table (%Y returns year as 4-digit value).
       Set CurrentYear = DATE_FORMAT(curdate(), '%Y');
 
               -- Get the current month (%m returns month as 2-digit value).
       SET CurrentMth = DATE_FORMAT(curdate(), '%m');
      
                -- The entire Table name
       Set CurrentMthTable = CONCAT_WS("_", "data_1", CurrentYear, CurrentMth);
 
DROP TABLE IF EXISTS db.DATA;

    CREATE TABLE db.DATA (
            ts DATETIME,
            tagid INT(11) NOT NULL,
            tagpath VARCHAR(255),
            curvalue FLOAT,
            t_stamp BIGINT(20) NOT NULL
     ); 

INSERT INTO db.DATA (
        ts, 
        tagid, 
        tagpath, 
        curvalue, 
        t_stamp) 

SELECT
    FROM_UNIXTIME((CONCAT_WS(“”, CurrentMthTable, “.t_stamp”)/1000)),
    CONCAT_WS("", CurrentMthTable, ".tagid"),
    te.tagpath,
    Case WHEN (CONCAT_WS("", CurrentMthTable, ".FloatValue")) IS Null then (CONCAT_WS("", CurrentMthTable, ".intValue")) else (CONCAT_WS("", CurrentMthTable, ".floatvalue")) END, 
    CONCAT_WS("", CurrentMthTable, ".t_stamp") 
        FROM CurrentMthTable, te 
        where (CONCAT_WS("", CurrentMthTable, ".tagid"))=te.id
        )); 
 
END //
DELIMITER ;

标签： mysqlmysql-workbench

解决方案

对于它的价值，SQL 不能使用替代变量来替代表、列、数据库、索引、存储过程等模式项的名称。它就是不能。这不仅仅是 MYSql 的限制，它适用于各种 SQL 数据库服务器。

正如您所发现的，您可以使用 MySQL “服务器端准备好的语句”来做您需要的事情。这是一种使用字符串处理来创建 SQL 语句，然后执行它们的方法。

如果在存储过程中执行此操作，则需要编写 SQL 字符串处理代码来创建 SQL 文本。像这样将上个月的表复制到本月的表中，然后擦除数据？

    DECLARE stmt VARCHAR(16000);
    SELECT CONCAT_WS("", 
            "CREATE TABLE data_1_",
            DATE_FORMAT(NOW(), '%Y_%c'),
            " AS SELECT * FROM data_1_",
            DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y_%c'), ';')
      INTO stmt;
    /* now stmt is CREATE TABLE data_1_2021_2 AS SELECT * FROM data_1_2021_1; */
    PREPARE s1 FROM stmt;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;
    SELECT CONCAT_WS("", 
            "TRUNCATE TABLE data_1_",
            DATE_FORMAT(NOW(), '%Y_%c'),';')
      INTO stmt;
    /* now stmt is TRUNCATE TABLE data_1_2021_2;  */
    PREPARE s1 FROM stmt;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;

我的要点：即使对于简单的东西，这也很难正确编程。而且很难读到您在将来进行故障排除时将难以推理。

而且，就其价值而言，您最好使用一个表格来存储来自所有仪器的所有数据，而不是每个月都使用一个表格。严重地。从单个大型且正确索引的表中访问记录会在数据库服务器上产生大致相同的负载，更易于编程，并且可以从几个月到几年甚至几十年优雅地扩展。每个月的一张桌子是不明智的。

推荐阅读