mysql - 如果不存在如何添加新列

Question

我想编写一个过程，如果该列不存在，则向表中添加一个新列。我一开始是为 MySQL 编写的，它运行成功，但是当我尝试为 SQL SERVER 编写相同的过程时，我遇到了一些语法错误，而且我不确定 SQL SERVER 过程是否正确并且是否会完成相同的工作MySQL 的一个。

MySQL 程序:

SET @dbname = DATABASE();
SET @tablename = "my_table";
SET @columnname = "my_field";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT UNSIGNED NOT NULL;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SQL SERVER 过程:

DECLARE @dbname varchar(128), @tablename varchar(11), @columnname varchar(11), @preparedStatement varchar(max);
SET @dbname = DB_NAME();
SET @tablename = "my_table";
SET @columnname = "my_field";
SET @preparedStatement = (SELECT CASE(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT UNSIGNED NOT NULL;")
));
EXEC sp_prepare @preparedStatement;

我在第 12 行遇到 GREATER THAN 运算符的语法错误:

Msg 102, Level 15, State 1, Line 12 Incorrect syntax near '>'.

Answer 1

你的 SQL 有很多错误:

首先，SQL Server 对文字字符串使用单引号 (')。

CASE 表达式使用格式 CASE WHEN {expr} THEN {expr} ELSE [WHEN {expr} THEN {expr}] [THEN {expr}] END 或CASE {expr} WHEN ...;您的版本格式非常错误。

SQL Server 中不存在

UNSIGNED 数据类型。您只签名了(tinyint 除外，它是 0-255)。

此外，sp_prepare 不是正确的函数，您需要 sp_executesql。

最后，我用 QUOTENAME 包裹了对象名称以避免注入(inject)，并将数据类型更改为 sysname(又名 nvarchar(128)) 并将文字字符串设为 nvarchar(N'' 而不是 '')。这让你:

DECLARE @dbname sysname,
        @tablename sysname,
        @columnname sysname,
        @preparedStatement nvarchar(MAX);
SET @dbname = DB_NAME();
SET @tablename = N'my_table';
SET @columnname = N'my_field';
SET @preparedStatement = CASE
                              WHEN (SELECT COUNT(*)
                                    FROM INFORMATION_SCHEMA.COLUMNS
                                    WHERE (TABLE_NAME = @tablename)
                                      AND (TABLE_SCHEMA = @dbname)
                                      AND (COLUMN_NAME = @columnname)) = 0 THEN CONCAT(N'ALTER TABLE ', QUOTENAME(@tablename), N' ADD ', QUOTENAME(@columnname), N' INT NOT NULL;')
                         END;
EXEC sp_executesql @preparedStatement;