INSERT (column1,column2) VALUES(<column 1 row 1>, <column 2 row 1>),(<column 1 row 2>, <column 2 row 2>)
這沒什麽特別的,但是我們將在本文中介紹使用數組進行INSERT的另一種方法,該方法使用MariaDB API將程序中的數組傳遞給MariaDB。這中方法將數據加載到MariaDB的速度非常快。
首先,讓我們看一下我們使用C編程API來訪問mariadb服務器。之所以使用C API是因為該API是圍繞MariaDB協議的精簡包,因此對C API的說明還同時可以介紹該協議本身可能實現的功能。其他連接器,例如JDBC,ODBC和Node.js具有各種功能級別,在某些情況下還具有與MariaDB進行交互的其他方式,但這僅發生在連接器本身內部。
下麵介紹的一種API是基於文本的,這是原始的MariaDB API。在此API中,所有數據均以文本形式發送和接收。讓我們先看一個示例Table。
CREATE TABLE customers(id INTEGER NOT NULL PRIMARY KEY,
cust_name VARCHAR(255),
cust_regdate DATETIME NOT NULL,
cust_numorders INTEGER NOT NULL);
現在,讓我們看一個簡單的程序,該程序使用基於文本的原始API向該表中插入一些行:
#include <stdio.h>
#include <mysql.h>
int main(int argc, char *argv[])
{
MYSQL *conn;
conn = mysql_init(NULL);
if(mysql_real_connect(conn, "localhost", "root", NULL, "blog", 3306,
"/var/lib/mysql/mysql.sock", CLIENT_INTERACTIVE) == NULL)
{
fprintf(stderr, "Error: %s\n", mysql_error(conn));
return 1;
}
if(mysql_query(conn, "INSERT INTO customers VALUES(1, 'Joe Bloggs',"
"'2019-03-05 14:30:00', 0)") != 0)
{
fprintf(stderr, "Error: %s\n", mysql_error(conn));
return 1;
}
if(mysql_query(conn, "INSERT INTO customers VALUES(2, 'Homer Simpson',"
"'2019-04-12 09:15:00', 0)") != 0)
{
fprintf(stderr, "Error: %s\n", mysql_error(conn));
return 1;
}
mysql_close(conn);
return 0;
}
看起來很簡單,首先初始化連接句柄並進行連接,然後使用2條INSERT語句插入兩行。我們傳遞的所有列,無論是字符串,整數還是日期都表示為字符串。我們也可以通過在單個SQL語句中傳遞所有行來使這種INSERT更有效,如下所示:
#include <stdio.h>
#include <mysql.h>
int main(int argc, char *argv[])
{
MYSQL *conn;
conn = mysql_init(NULL);
if(mysql_real_connect(conn, "localhost", "root", NULL, "blog", 3306,
"/var/lib/mysql/mysql.sock", CLIENT_INTERACTIVE) == NULL)
{
fprintf(stderr, "Error: %s\n", mysql_error(conn));
return 1;
}
if(mysql_query(conn, "INSERT INTO customers VALUES(1, 'Joe Bloggs',"
"'2019-03-05 14:30:00', 0),(2, 'Homer Simpson',"
"'2019-04-12 09:15:00', 0)") != 0)
{
fprintf(stderr, "Error: %s\n", mysql_error(conn));
return 1;
}
mysql_close(conn);
return 0;
}
MariaDB API中的Prepared語句
Prepared語句API與基於文本的API不同:首先,我們不將數據作為SQL語句的一部分傳遞,而是在SQL語句中包含要在其中放置數據的占位符;然後將這些占位符與程序變量相關聯,此過程稱為捆綁,變量中才是實際數據。
相同的SQL語句隻需要準備(Prepare)一次,在此之後,我們可以執行多次,隻需將程序變量數據更改即可。綁定過程不僅需要知道對其綁定的變量的引用,而且還需要知道一些其他信息,例如所引用的數據類型、數據長度以及所謂的指示變量。其中指示變量補充說明了所引用的數據變量的信息,例如它是否為NULL、所引用的字符串是否為NULL終止,或者是否將長度作為字符串的實際長度。示例如下:
#include <stdio.h>
#include <string.h>
#include <mysql.h>
int main(int argc, char *argv[])
{
MYSQL *conn;
MYSQL_STMT *stmt;
int id;
char id_ind;
char name[256];
char name_ind;
long name_len;
MYSQL_TIME regdate;
char regdate_ind;
int numorders;
char numorders_ind;
MYSQL_BIND bind[4];
conn = mysql_init(NULL);
if(mysql_real_connect(conn, "localhost", "root", NULL, "blog", 3306,
"/var/lib/mysql/mysql.sock", CLIENT_INTERACTIVE) == NULL)
{
fprintf(stderr, "Error: %s\n", mysql_error(conn));
return 1;
}
stmt = mysql_stmt_init(conn);
if(mysql_stmt_prepare(stmt, "INSERT INTO customers VALUES(?, ?, ?, ?)", -1) != 0)
{
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
return 1;
}
memset(bind, 0, sizeof(bind));
bind[0].u.indicator = &id_ind;
bind[0].buffer_type = MYSQL_TYPE_LONG;
bind[0].buffer = &id;
bind[1].u.indicator = &name_ind;
bind[1].buffer_type = MYSQL_TYPE_STRING;
bind[1].buffer = name;
bind[1].length = &name_len;
name_len = -1;
bind[2].u.indicator = ®date_ind;
bind[2].buffer_type = MYSQL_TYPE_DATETIME;
bind[2].buffer = ®date;
bind[3].u.indicator = &numorders_ind;
bind[3].buffer_type = MYSQL_TYPE_LONG;
bind[3].buffer = &numorders;
if(mysql_stmt_bind_param(stmt, bind) != 0)
{
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
return 1;
}
id_ind = regdate_ind = numorders_ind = STMT_INDICATOR_NONE;
name_ind = STMT_INDICATOR_NTS;
id = 1;
strcpy(name, "Joe Bloggs");
regdate.year = 2019;
regdate.month = 3;
regdate.day = 5;
regdate.hour = 14;
regdate.minute = 30;
numorders = 0;
if(mysql_stmt_execute(stmt) != 0)
{
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
return 1;
}
id = 2;
strcpy(name, "Homer Simpson");
regdate.year = 2019;
regdate.month = 4;
regdate.day = 12;
regdate.hour = 9;
regdate.minute = 15;
numorders = 0;
if(mysql_stmt_execute(stmt) != 0)
{
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
return 1;
}
mysql_close(conn);
return 0;
}
批量加載–帶輸入數組的Prepare語句
如果要一次性插入兩行或更多行,需要準備並執行以下操作:
INSERT INTO customers VALUES(?, ?, ?, ?),(?, ?, ?, ?)
那麽,按照示例中Prepare語句的用法,我們需要綁定8個程序變量,這似乎並不十分靈活,對吧?您將需要根據要插入的行數準備不同的語句,這太笨拙。實際上有更好的方法,即使用數組綁定。也就是每個綁定程序變量都是一個值數組,這樣可以用一條語句插入任意數量的行。還是用一個示例來說明具體做法:
#include <stdio.h>
#include <string.h>
#include <mysql.h>
int main(int argc, char *argv[])
{
MYSQL *conn;
MYSQL_STMT *stmt;
int id[2];
char id_ind[2];
char *nameptr[2];
char name[2][256];
char name_ind[2];
long name_len[2];
MYSQL_TIME *regdateptr[2];
MYSQL_TIME regdate[2];
char regdate_ind[2];
int numorders[2];
char numorders_ind[2];
MYSQL_BIND bind[4];
unsigned int numrows;
conn = mysql_init(NULL);
if(mysql_real_connect(conn, "localhost", "root", NULL, "blog", 3306,
"/var/lib/mysql/mysql.sock", CLIENT_INTERACTIVE) == NULL)
{
fprintf(stderr, "Error: %s\n", mysql_error(conn));
return 1;
}
stmt = mysql_stmt_init(conn);
if(mysql_stmt_prepare(stmt, "INSERT INTO customers VALUES(?, ?, ?, ?)", -1) != 0)
{
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
return 1;
}
memset(bind, 0, sizeof(bind));
bind[0].u.indicator = id_ind;
bind[0].buffer_type = MYSQL_TYPE_LONG;
bind[0].buffer = id;
bind[1].u.indicator = name_ind;
bind[1].buffer_type = MYSQL_TYPE_STRING;
bind[1].buffer = nameptr;
bind[1].length = name_len;
bind[2].u.indicator = regdate_ind;
bind[2].buffer_type = MYSQL_TYPE_DATETIME;
bind[2].buffer = regdateptr;
bind[3].u.indicator = numorders_ind;
bind[3].buffer_type = MYSQL_TYPE_LONG;
bind[3].buffer = numorders;
if(mysql_stmt_bind_param(stmt, bind) != 0)
{
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
return 1;
}
memset(®date, 0, sizeof(regdate));
id_ind[0] = regdate_ind[0] = numorders_ind[0] = STMT_INDICATOR_NONE;
name_ind[0] = STMT_INDICATOR_NTS;
id[0] = 1;
nameptr[0] = name[0];
strcpy(name[0], "Joe Bloggs");
name_len[0] = 256;
regdateptr[0] = ®date[0];
regdate[0].year = 2019;
regdate[0].month = 3;
regdate[0].day = 5;
regdate[0].hour = 14;
regdate[0].minute = 30;
numorders[0] = 0;
id_ind[1] = regdate_ind[1] = numorders_ind[1] = STMT_INDICATOR_NONE;
name_ind[1] = STMT_INDICATOR_NTS;
id[1] = 2;
nameptr[1] = name[1];
strcpy(name[1], "Homer Simpson");
name_len[1] = 256;
regdateptr[1] = ®date[1];
regdate[1].year = 2019;
regdate[1].month = 4;
regdate[1].day = 12;
regdate[1].hour = 9;
regdate[1].minute = 15;
numorders[1] = 0;
numrows = 2;
mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, &numrows);
if(mysql_stmt_execute(stmt) != 0)
{
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
return 1;
}
mysql_close(conn);
return 0;
}
這裏有幾個要注意的要點。首先,當我們綁定到數組時,任何字符類型為char *字符串或MYSQL_TIME的數據類型都必須是指針數組,您可以在上麵的代碼中看到這一點。這雖然使代碼看起來有些複雜,但是也算是是一個優點,因為綁定的數據可以位於任意地方(例如,每一行可以是某個類的成員或struct的某個字段)。
結論
將數據作為程序數組加載到MariaDB有多個優點,它在編程上比單個數組字符串更易於處理,尤其是如果後者包含多行數據;對齊包含在類或結構體中的數據也更加容易,從而實現更好的代碼集成。最後,性能要好一些,尤其是當有許多行數據要插入的時候。