在 C# 中使用 MySQL 命令动态创建表 [英] Create table dynamically with MySQL command in c#

问题描述

我想动态创建表并用随机数填充它，但在执行代码时，MySQL抛出语法错误.

 for (int i = 0; i <100; i++){//gameSession 是 int，我尝试使用 Parameters.AddWithValue 但仍然有同样的问题.MySqlCommand dice = new MySqlCommand("INSERT INTO "+gameSession.ToString()+" (Dice1,Dice2) Values (@dice1,@dice2)", myConnection);随机骰子1 = new Random();dice.Parameters.AddWithValue("@dice1", dice1.Next(1, 7));随机骰子2 = new Random();dice.Parameters.AddWithValue("@dice2", dice2.Next(1, 7));如果(myConnection.State == ConnectionState.Closed){myConnection.Open();}骰子.ExecuteNonQuery();}

<块引用>

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 INSERT INTO '7571877 ...

附近使用的正确语法

解决方案

语法错误的一个主要原因是表没有被引用.查看MySQL 标识符手册(包括表名).

就像默认情况下你不能真正使用数字来表示变量一样，你不能在不转义标识符的情况下对表做同样的事情.几种解决方案是在表名前加上一个字母或使用单个表.

回答标题中的问题:

如果没有，您可以创建表格存在使用IF NOT EXISTS"子句.SQL 看起来像这样:

CREATE TABLE table_name IF NOT EXISTS (骰子1整数，骰子 2 整数);

如果你坚持走这条路，你应该用标准的前缀字母创建你的表名:

"s" + gameSession.ToString()

<块引用>

请注意，为每个会话使用单独的表确实会使您的数据库维护需求复杂化，尤其是在处理废弃会话时.删除单个表中的行比查找所有被放弃的表容易得多.

另一个严重的问题与数据库安全有关.在针对数据库构建应用程序时，只授予用户插入和更新权限比授予他们在数据库中创建和删除任何表的能力要好得多.由于表名是动态创建的，因此您无法真正轻松地维护每个表的权限.如果这是面向网络的，您将承担一些严重的责任，而收益却微乎其微.

满足需求:

更好的设计是创建一个带有会话 ID 的表.否则，您将拥有任意数量的表，每个表只有一个记录.您将像这样创建一次表:

如果不存在则创建表 DiceRolls (会话整数，骰子1整数，骰子 2 整数);

你的插入只需要一点点改变:

 for (int i = 0; i <100; i++){//gameSession 是 int，我尝试使用 Parameters.AddWithValue 但仍然有同样的问题.MySqlCommand dice = new MySqlCommand("INSERT INTO DiceRolls (Session, Dice1,Dice2) Values (@session,@dice1,@dice2)", myConnection);dice.Parameters.AddWithValue("@session", gameSession);随机骰子1 = new Random();dice.Parameters.AddWithValue("@dice1", dice1.Next(1, 7));随机骰子2 = new Random();dice.Parameters.AddWithValue("@dice2", dice2.Next(1, 7));如果(myConnection.State == ConnectionState.Closed){myConnection.Open();}骰子.ExecuteNonQuery();}

这让清理工作变得更容易，因为您可以批量作业删除所有非当前会话的内容.

I want to create table dynamically and fill it with random numbers, but when execute code, MySQL throw an error about syntax.

 for (int i = 0; i < 100; i++)
        {
            //gameSession is int, i tried use Parameters.AddWithValue but still have same problem.
            MySqlCommand dice = new MySqlCommand("INSERT INTO "+gameSession.ToString()+" (Dice1,Dice2) Values (@dice1,@dice2)", myConnection);


            Random dice1 = new Random();
            dice.Parameters.AddWithValue("@dice1", dice1.Next(1, 7));
            Random dice2 = new Random();
            dice.Parameters.AddWithValue("@dice2", dice2.Next(1, 7));
            if (myConnection.State == ConnectionState.Closed)
            {
                myConnection.Open();
            }
            dice.ExecuteNonQuery();
        }

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near INSERT INTO '7571877 ...

解决方案

One major reason you have an error in your syntax is that the table is not quoted. Check out the MySQL manual for identifiers (which includes table names).

Just like you can't really use numbers by default to represent variables, you can't do the same for tables without escaping the identifiers. A couple solutions for you would be to prefix the table name with a letter or to use a single table.

Answering the question in your title:

You can create a table if it doesn't exist using the "IF NOT EXISTS" clause. The SQL would look like this:

CREATE TABLE table_name IF NOT EXISTS (
    dice1 INTEGER,
    dice2 INTEGER
);

If you insist on going down this route, you should create your table names with a standard prefix letter:

"s" + gameSession.ToString()

Be warned, having a separate table for each session does complicate your database maintenance needs, particularly for dealing with abandoned sessions. It's a lot easier to remove rows in a single table than find all tables that are abandoned.

Another serious concern has to do with database security. When building an application against a database, it is far better to only grant insert and update privileges to a user than it is to grant them the ability to create and drop any table in your database. Since the table names are dynamically created, you can't really maintain per-table privileges easily. If this is web facing you are opening yourself up to some serious liability for very little gain.

Answering the need:

A better design is to create a single table with a session id. Otherwise, you will have any number of tables with one record each. You would create the table once like this:

CREATE TABLE DiceRolls IF NOT EXISTS (
    session INTEGER,
    dice1 INTEGER,
    dice2 INTEGER
);

Your inserts would only need to change a little bit:

 for (int i = 0; i < 100; i++)
    {
        //gameSession is int, i tried use Parameters.AddWithValue but still have same problem.
        MySqlCommand dice = new MySqlCommand("INSERT INTO DiceRolls (Session, Dice1,Dice2) Values (@session,@dice1,@dice2)", myConnection);

        dice.Parameters.AddWithValue("@session", gameSession);
        Random dice1 = new Random();
        dice.Parameters.AddWithValue("@dice1", dice1.Next(1, 7));
        Random dice2 = new Random();
        dice.Parameters.AddWithValue("@dice2", dice2.Next(1, 7));
        if (myConnection.State == ConnectionState.Closed)
        {
            myConnection.Open();
        }
        dice.ExecuteNonQuery();
    }

This makes clean up a lot easier as you can batch job something that deletes everything that isn't a current session.

这篇关于在 C# 中使用 MySQL 命令动态创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！