Solved MySQL multi-value insert using prepared statements

I would suggest to add such feature as multi-value inserts using prepared statements.
So instead of executing 500 queries:

Code:

INSERT INTO `table` VALUES (...);

We can execute just one with 500 values:

Code:

INSERT INTO `table` VALUES (...), (...), (...), (...), (...), ...;

This is extremely useful when working with big data, takes much less time and consumes less resources.

 

correct me if i am wrong but if you are just talking about something that is normally handled in a single Sql statement then it should be possible right now.

C# Code

Code:

private readonly Ext.MySql.Libraries.MySql mySql = Interface.GetMod().GetLibrary<Ext.MySql.Libraries.MySql>();
private Ext.MySql.Connection MySqlConnection;
MySqlConnection = mySql.OpenDb(dbHost, dbPort, dbName, dbUsername, dbPassword);
Sql sql = new Sql("INSERT INTO `table` VALUES (...), (...), (...), (...), (...), ...");
mySql.Insert(sql, MySqlConnection);

 

It's possible, of course. But the developer is still must build the query string.

Just for an example, how such thing would look on Java. Not sure that something similar are present in .NET.

Code:

PreparedStatement st = con.prepareStatement("INSERT INTO `logging` VALUES (?,?,?,?);");
Log log;
while ((log = _logPool.poll()) != null)
{
st.setString(1, log.date);
st.setShort(2, log.type);
st.setShort(3, log.id);
st.setInt(4, log.info);
st.addBatch();
}
st.executeBatch();
st.close();

 

One of my test plugins, if it will help you:

Code:

// Reference: Oxide.Ext.MySqlusing System.Text;using Oxide.Core;namespace Oxide.Plugins
{
[Info("MySqlTest", "Nogrod", "1.0.0")]
internal class MySqlTest : RustPlugin
{
    private readonly Ext.MySql.Libraries.MySql _mySql = Interface.GetMod().GetLibrary<Ext.MySql.Libraries.MySql>();
    private Ext.MySql.Connection _mySqlConnection;
    private const string InsertData = "INSERT INTO example (`data`, `data_other`) VALUES (@0, @1);";
    private const string SelectData = "SELECT `id`, `data`, `data_other` FROM example;";        private void OnServerInitialized()
    {
        _mySqlConnection = _mySql.OpenDb("localhost", 3306, "rust", "root", "");            var sql = Ext.MySql.Sql.Builder.Append(@"DROP TABLE IF EXISTS example;
                            CREATE TABLE IF NOT EXISTS example (
                                id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                                data VARCHAR(100),
                                data_other INT
                            );");            var r = new System.Random();
        for (var i = 0; i < 10; i++)
        {
            var rInt = r.Next();
            sql.Append(InsertData, "test" + rInt, rInt);
        }
        _mySql.InsertAsync(sql, _mySqlConnection);            sql = Ext.MySql.Sql.Builder.Append(SelectData);
        _mySql.QueryAsync(sql, _mySqlConnection, list =>
        {
            if (list == null) return;
            var sb = new StringBuilder();
            sb.AppendLine();
            sb.AppendLine(" id\tdata\t\tdata_other");
            foreach (var entry in list)
            {
                sb.AppendFormat(" {0}\t{1}\t{2}", entry["id"], entry["data"], entry["data_other"]);
                sb.AppendLine();
            }
            Puts(sb.ToString());
        });
    }
}
}

PS: the QueryAsync currently throws an error if called right after the InsertAsync...will be fixed with the next snapshot

 

take a look at this resource as well. it is not oxide specific so you will need to modify the declarations a little, but should lead you down the right path.

http://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html

 

Ideally you should avoid constructing SQL statements whatsoever. This could be a limitation of the MySQL extension or Mono, but using DataAdatper with DataTable or DataSet means you can insert/update/select data through managed APIs without ever building a single SQL statement. Escaping strings etc is taken care of by .Net so you're going to get the best security and performance.

 

You want to issue the sql statement "START TRANSACTION" before you do your mass inserts/deletes/updates the reason is because typical inserts write to the disk per one, which as you can imagine is slow for slow harddrives, and doing transactions disables the per write. After you've finished issuing all your inserts then do "COMMIT". This will write all at once and makes mass inserts tremendously fast! I did a test with thousands of inserts and it dropped the time from seconds to low milliseconds...

 

I'm pretty sure the MySQL connector does cache, the cache is probably just too small for thousands of large queries.
For small insert bulks however using transactions assures database integrity.