如何使用C＃复制MySql数据库架构？ [英] How to copy a MySql database schema using C#?

问题描述

我正在尝试使用C＃& MySql为了复制一个空表（真的重新创建了Schema）。结构如下：

I'm trying to use C# & MySql in order to copy an empty table (recreate the Schema really). The structure looks like this:

> TableTemplate (schema)
    + Tables
        > FirstTable    (table)
        > second table  (table)
        > ...

> SomeOtherTable
    + Tables
        > ... 

我想复制 TableTemplate

What I would like is to copy the TableTemplate into a new Schema with the user name.

第一个明显的遗忘路径是尝试 CREATE TABLE @UserName Like TableTemplate ，迅速得知应该将sql参数用于值而不是表名（再次是所有的jon skeet，如何将表作为参数传递给MySqlCommand？）。

The first obvious path to oblivion was trying CREATE TABLE @UserName LIKE TableTemplate, swiftly learning that sql parameters are supposed to be used for values and not table names (all hail jon skeet, again: How to pass a table as parameter to MySqlCommand?).

因此，我们可以手动验证用户名以构建表名（ 罗伯特是一个很好的例子）。

So that leaves us with manual validation of the user names in order to build the table names (robert's a prime example).

接下来，似乎甚至 CREATE TABLE UserID Like TableTemplate; 也不起作用（即使在MySQL Workbench中也是如此），因为 TableTemplate 无效

Next, it seems that even CREATE TABLE UserID LIKE TableTemplate; won't work (even from MySQL Workbench), since TableTemplate isn't a table.

因此，要写一个循环来创建一个表，该循环将像 Like 中的每个表一样code> TableTemplate ，在创建 UserID 模式（手动验证该字符串后）之后，或尝试其他选项，例如转储数据库并创建一个新选项，如这些问题所示：

So It's down to writing a loop that will create a table LIKE each table in TableTemplate, after creating a UserID Schema (after manual validation of that string), or trying other options like dumping the database and creating a new one, as seen in these questions:

• C＃和mysqldump


• 使用C＃中的mysqldump降低性能

• C# and mysqldump
• Slow performance using mysqldump from C#

但是我宁愿避免运行进程，转储数据库并在每次添加用户时从那里创建数据库。

But I would prefer avoid running a process, dumping the database, and creating it from there every time I add a user.

任何建议将不胜感激。

推荐答案

最终使用类似这样的方法，其中 aName 传递给表名：

Ended up using something like this, in a method where aName is passed for the table name:

using (MySqlCommand cmd = new MySqlCommand(string.Format("CREATE DATABASE {0} ;", aName), connection))
{
    cmd.ExecuteNonQuery();          // Create the database with the given user name

    // Building the sql query that will return a "create table" per table in some_db template DB.
    cmd.CommandText = (string.Format("SELECT CONCAT (\"CREATE TABLE {0}.\", TABLE_NAME ,\" "
                                       + "LIKE some_other_db.\", TABLE_NAME ) as creation_sql "
                                       + "FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'some_db';"
                                    , aName));

    try     // Building the inner tables "create table" sql strings
    {
        using (MySqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
                createInnerTablesList.Add(reader.GetString(0));
        }
    }
    catch (MySqlException mysql_ex) { ... } // handle errors 

    foreach (var sql_insert_query in createInnerTablesList)
    {
        try                         // Insert the tables into the user database
        {
            cmd.CommandText = sql_insert_query;
            cmd.ExecuteNonQuery();
        }
        catch (Exception e) { ... } // handle errors 
    }
} 

使用 Like 与 AS 之类的原因是因为Jungsu建议即使 AS 将创建表，它也不会保留任何已定义的约束和键（主键等）。

The reasons for using LIKE vs AS like Jungsu suggested is that even though the AS will create the tables, it will not keep any of the constraints and keys defined (primary key, etc).

使用 Like 会将它们复制为约束。

Using the LIKE will replicate them with the constraints.

对此我仍然不太满意，因为我觉得我虽然缺少一些东西...

I'm still not too happy about this, since I feel I'm missing something though ...

这篇关于如何使用C＃复制MySql数据库架构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！