创建表如果不存在，插入列如果缺少，最好的方法

I need to create a table if it doesnt exist, and add missing columns in the proper order if the table already exists.

如果表不存在，我需要创建一个表，如果表已经存在，则按适当的顺序添加缺失的列。

I know how to do it with lots of queries, and if statements and so on, but what I am asking here is what the best solution would be.. Maybe there is a special query to do this, or a smart way.

我知道如何使用大量查询、if语句等等，但是我想问的是最好的解决方案是什么。也许有一个特殊的查询可以完成这个任务，或者有一个聪明的方法。

I would do it this way:

我会这样做:

create table if not exists (all columns as they should be)

如果不存在，则创建表(应该是所有列)
compare all the columns (if some are missing they will be added, else not)

比较所有列(如果缺少一些列，将添加它们，否则不添加)

Is this the best way or are there better ways to do it?

这是最好的方法还是更好的方法?

ADDITIONAL INFO

额外的信息

the colums need to be added at the right position. I have a list of strings representing all the columns in the proper order. using vb.net I am iterating through these strings.

需要在正确的位置添加列。我有一个字符串列表，以正确的顺序表示所有的列。使用vb.net，我正在遍历这些字符串。

2 个解决方案

#1

Check out this for instance. It's basically about querying the data dictionary and adding columns only if they do not exist:

以这个为例。它基本上是关于查询数据字典和只在不存在的情况下添加列:

IF NOT EXISTS(SELECT NULL
                FROM INFORMATION_SCHEMA.COLUMNS
               WHERE table_name = 'tablename'
                 AND table_schema = 'db_name'
                 AND column_name = 'columnname') THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;

Putting it in a procedure makes it quite handy.

把它放在程序中会很方便。

p.s. note about column positions: from the docs

注:列位:从文档中

To add a column at a specific position within a table row, use FIRST or AFTER col_name. The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.

要在表行的特定位置添加列，请使用col_name前或后。默认情况是最后添加列。您还可以在更改或修改操作时使用FIRST和AFTER来重新排序表中的列。

#2

-1

You can use following codes for that:

你可以使用以下代码:

if not exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'table_name' and COLUMN_NAME = 'column_name')

BEGIN
    ALTER TABLE table_name ADD
    ToUser uniqueidentifier NULL

END

#1