具有动态表名或重新设计的 SQL 语句? [英] SQL-Statement with dynamic table-names or redesign?

问题描述

我有一个 MS SQL 2008 数据库，用于存储用于创建加权无向图的数据.数据存储在具有以下结构的表中:

I have a MS SQL 2008 database which stores data for creating a weighted, undirected graph. The data is stored in tables with the following structure:

[id1] [int] NOT NULL,
[id2] [int] NOT NULL,
[weight] [float] NOT NULL

其中 [id1] 和 [id2] 表示两个连接的节点，[weight] 表示连接这些节点的边的权重.

where [id1] and [id2] represents the two connected nodes and [weight] the weight of the edge that connects these nodes.

有几种不同的算法，可以根据一些基本数据创建图形.对于每种算法，我想将图形数据存储在单独的表中.这些表都具有相同的结构(如上所示)并使用指定的前缀(similarityALB、similaritybyArticle、similaritybyCategory...)，因此我可以将它们识别为图形表.

There are several different algorithms, that create the graph from some basic data. For each algorithm, I want to store the graph-data in a separate table. Those tables all have the same structure (as shown above) and use a specified prefix (similarityALB, similaritybyArticle, similaritybyCategory, ...) so I can identify them as graph-tables.

客户端程序可以选择哪个表(即通过哪种算法创建图形)用于进一步的操作.

The client program can select, which table (i.e. by which algorithm the graph is created) to use for the further operations.

对数据的访问是通过存储过程完成的.由于我有不同的表，我需要使用一个变量表名，例如:

Access to the data is done by stored procedures. As I have different tables, I would need to use a variable tablename e.g.:

SELECT id1, id2, weight FROM @tableName

这不起作用，因为 SQL 不支持语句中的变量表名.我在网上搜索过这个问题的所有解决方案都使用动态 SQL EXEC() 语句，例如:

This doesn't work because SQL doesn't support variable tablenames in the statement. I have searched the web and all solutions to this problem use the dynamic SQL EXEC() statement e.g.:

EXEC('SELECT id1, id2, weight FROM ' + @tableName)

正如他们中的大多数人提到的，这使得语句容易受到 SQL 注入，我想避免这种情况.一个简单的重新设计想法是将所有不同的图表放在一个表格中，并添加一列来标识不同的图表.

As most of them mentioned, this makes the statement prone to SQL-injection, which I'd like to avoid. A simple redesign idea would be to put all the different graphs in one table and add a column to identify the different graphs.

[graphId] [int] NOT NULL,
[id1] [int] NOT NULL,
[id2] [int] NOT NULL,
[weight] [float] NOT NULL

我对这个解决方案的问题是，根据所使用的算法(最多 5 亿个条目)，图表可能非常大.我需要在 (id1, id2) 和 (id2, id1) 上对表进行索引.现在将它们全部放在一张大桌子上会使桌子更大(并且请求速度更慢).由于活动索引，添加新图会导致性能不佳.TRUNCATE 无法删除图形了，我需要使用

My problem with this solution is, that the graphs can be very large depending on the used algorithm (up to 500 Million entries). I need to index the table over (id1, id2) and (id2, id1). Now putting them all in one big table would makes the table even huger (and requests slower). Adding a new graph would result in bad performance, because of the active indicees. Deleting a graph could not be done by TRUNCATE anymore, I would need to use

DELETE * FROM myTable WHERE graphId=@Id

它在处理大表时表现非常糟糕，并创建了一个非常大的日志文件(当图表足够大时，这将超过我的磁盘空间).所以我想为每个图表保留独立的表格.

which performs very bad with large tables and creates a very large logfile (which would exceed my disk space when the graph is big enough). So I'd like to keep the independent tables for each graph.

有什么建议可以通过找到一种方法来参数化表名或在避免上述问题的同时重新设计数据库结构来解决这个问题吗?

Any suggestions how to solve this problems by either find a way to parametrize the tablename or to redesign the database structure while avoiding the aforementioned problems?

推荐答案

也许我没有完全理解，但是:

Maybe I did not understand everything, but:

CREATE PROCEDURE dbo.GetMyData (
     @TableName AS varchar(50)
    )
AS 
BEGIN
    IF @TableName = 'Table_1' 
        BEGIN
            SELECT  id1
                   ,id2
                   ,[weight]
            FROM    dbo.Table_1
        END

    IF @TableName = 'Table_2' 
        BEGIN
            SELECT  id1
                   ,id2
                   ,[weight]
            FROM    dbo.Table_2
        END
END 

然后:

EXEC dbo.GetMyData @TableName = 'Table_1' 

一种不同的技术涉及动态使用同义词，例如:

A different technique involves using synonyms dynamically, for example:

DECLARE @TableName varchar(50)
SET @TableName = 'Table_1' 

-- drop synonym if it exists
IF object_id('dbo.MyCurrentTable', 'SN') IS NOT NULL 
    DROP SYNONYM MyCurrentTable ;

-- create synonym for the current table
IF @TableName = 'Table_1' 
    CREATE SYNONYM dbo.MyCurrentTable FOR dbo.Table_1 ;

IF @TableName = 'Table_2' 
    CREATE SYNONYM dbo.MyCurrentTable FOR dbo.Table_2 ;

-- use synonym
SELECT  id1, id2, [weight] 
FROM dbo.MyCurrentTable

这篇关于具有动态表名或重新设计的 SQL 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！