Есть несколько способов загрузки данных в платформу MariaDB , и некоторые из них лучше, чем другие. Два основных способа: использовать LOAD DATA INFILE / LOAD DATA LOCAL INFILE , что очень быстро, в частности, не-LOCAL, и тогда у нас есть простой оператор INSERT. При использовании оператора INSERT вы можете передать массив на сервер MariaDB, например так:
MariaDB SQL
xxxxxxxxxx
1
INSERT (column1,column2) VALUES(<column 1 row 1>, <column 2 row 1>),(<column 1 row 2>, <column 2 row 2>)
Ничего особенного в этом нет, но то, что мы расскажем в этом блоге, — это еще один способ выполнения INSERT с использованием массивов, который использует API MariaDB для передачи программного массива в MariaDB и который на самом деле является очень быстрым способом загрузки данных в MariaDB.
Для начала давайте рассмотрим два API, которые мы используем для доступа к серверу MariaDB из программы на Си . Причина, по которой уместен API C, заключается в том, что этот API представляет собой тонкую оболочку вокруг протокола MariaDB, поэтому объяснение C API также охватывает то, что возможно с самим протоколом. Другие соединители, такие как JDBC , ODBC и Node.js, имеют различные уровни функциональности и в некоторых случаях имеют другие способы взаимодействия с MariaDB, но тогда это просто происходит внутри самого соединителя.
Существует два API, один из которых основан на тексте, и это оригинальный API MariaDB. В этом API все данные отправляются и принимаются в виде текста. Давайте сначала посмотрим на пример таблицы, прежде чем приступить к рассмотрению кода.
SQL
xxxxxxxxxx
1
CREATE TABLE customers(id INTEGER NOT NULL PRIMARY KEY,
2
cust_name VARCHAR(255),
3
cust_regdate DATETIME NOT NULL,
4
cust_numorders INTEGER NOT NULL);
Вы также можете быть заинтересованы в: Mule 4: Массовая вставка коннектора базы данных
Теперь давайте посмотрим на простую программу, которая вставляет несколько строк в эту таблицу, используя оригинальный текстовый API:
Джава
xxxxxxxxxx
1
#include <stdio.h>
2
#include <mysql.h>
3
4
int main(int argc, char *argv[])
5
{
6
MYSQL *conn;
7
8
conn = mysql_init(NULL);
9
if(mysql_real_connect(conn, "localhost", "root", NULL, "blog", 3306,
10
"/var/lib/mysql/mysql.sock", CLIENT_INTERACTIVE) == NULL)
11
{
12
fprintf(stderr, "Error: %s\n", mysql_error(conn));
13
return 1;
14
}
15
16
if(mysql_query(conn, "INSERT INTO customers VALUES(1, 'Joe Bloggs',"
17
"'2019-03-05 14:30:00', 0)") != 0)
18
{
19
fprintf(stderr, "Error: %s\n", mysql_error(conn));
20
return 1;
21
}
22
23
if(mysql_query(conn, "INSERT INTO customers VALUES(2, 'Homer Simpson',"
24
"'2019-04-12 09:15:00', 0)") != 0)
25
{
26
fprintf(stderr, "Error: %s\n", mysql_error(conn));
27
return 1;
28
}
29
30
mysql_close(conn);
31
return 0;
32
}
Это достаточно просто, мы инициализируем дескриптор соединения и подключаемся, а затем вставляем две строки, используя 2 оператора INSERT. Все столбцы, которые мы передаем, будь то строки, целые числа или даты, представлены в виде строк. Мы можем сделать этот INSERT более эффективным, передав все строки в одном SQL-выражении, например так:
Джава
xxxxxxxxxx
1
#include <stdio.h>
2
#include <mysql.h>
3
4
int main(int argc, char *argv[])
5
{
6
MYSQL *conn;
7
8
conn = mysql_init(NULL);
9
if(mysql_real_connect(conn, "localhost", "root", NULL, "blog", 3306,
10
"/var/lib/mysql/mysql.sock", CLIENT_INTERACTIVE) == NULL)
11
{
12
fprintf(stderr, "Error: %s\n", mysql_error(conn));
13
return 1;
14
}
15
16
if(mysql_query(conn, "INSERT INTO customers VALUES(1, 'Joe Bloggs',"
17
"'2019-03-05 14:30:00', 0),(2, 'Homer Simpson',"
18
"'2019-04-12 09:15:00', 0)") != 0)
19
{
20
fprintf(stderr, "Error: %s\n", mysql_error(conn));
21
return 1;
22
}
23
24
mysql_close(conn);
25
return 0;
26
}
Подготовленное заявление MariaDB API
Подготовленный API оператора отличается от текстового API, но он содержится в той же библиотеке и в тех же функциях подключения, и многие другие функции используются таким же образом. Это отличается в нескольких отношениях, хотя. Во-первых, мы не передаем данные как часть оператора SQL, скорее, оператор SQL содержит заполнитель, где мы хотим, чтобы данные были, а затем мы связываем эти заполнители с программными переменными, процессом, называемым связыванием , где мы размещаем фактические данные.
Один и тот же оператор SQL нужно подготовить только один раз, после чего мы можем выполнить его несколько раз и просто изменить данные в наших программных переменных между ними. Для этого , чтобы работать, привязка процесс должен знать не только ссылку на переменную он является обязательным, но и несколько других вещей , как тип данных, которые ссылаются, длина его и то , что называется индикаторная переменная является нужно. И индикаторная переменная говорит что-то еще о ссылочных переменных, например, если она имеет значение NULL, а указанная строка имеет значение NULL, или если длина принимается за фактическую длину строки.
В качестве примера, давайте посмотрим, как будет выглядеть первая программа выше при использовании подготовленных операторов:
Джава
xxxxxxxxxx
1
#include <stdio.h>
2
#include <string.h>
3
#include <mysql.h>
4
5
int main(int argc, char *argv[])
6
{
7
MYSQL *conn;
8
MYSQL_STMT *stmt;
9
int id;
10
char id_ind;
11
char name[256];
12
char name_ind;
13
long name_len;
14
MYSQL_TIME regdate;
15
char regdate_ind;
16
int numorders;
17
char numorders_ind;
18
MYSQL_BIND bind[4];
19
20
conn = mysql_init(NULL);
21
if(mysql_real_connect(conn, "localhost", "root", NULL, "blog", 3306,
22
"/var/lib/mysql/mysql.sock", CLIENT_INTERACTIVE) == NULL)
23
{
24
fprintf(stderr, "Error: %s\n", mysql_error(conn));
25
return 1;
26
}
27
28
stmt = mysql_stmt_init(conn);
29
if(mysql_stmt_prepare(stmt, "INSERT INTO customers VALUES(?, ?, ?, ?)", -1) != 0)
30
{
31
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
32
return 1;
33
}
34
35
memset(bind, 0, sizeof(bind));
36
bind[0].u.indicator = &id_ind;
37
bind[0].buffer_type = MYSQL_TYPE_LONG;
38
bind[0].buffer = &id;
39
bind[1].u.indicator = &name_ind;
40
bind[1].buffer_type = MYSQL_TYPE_STRING;
41
bind[1].buffer = name;
42
bind[1].length = &name_len;
43
name_len = -1;
44
bind[2].u.indicator = ®date_ind;
45
bind[2].buffer_type = MYSQL_TYPE_DATETIME;
46
bind[2].buffer = ®date;
47
bind[3].u.indicator = &numorders_ind;
48
bind[3].buffer_type = MYSQL_TYPE_LONG;
49
bind[3].buffer = &numorders;
50
51
if(mysql_stmt_bind_param(stmt, bind) != 0)
52
{
53
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
54
return 1;
55
}
56
id_ind = regdate_ind = numorders_ind = STMT_INDICATOR_NONE;
57
name_ind = STMT_INDICATOR_NTS;
58
59
id = 1;
60
strcpy(name, "Joe Bloggs");
61
regdate.year = 2019;
62
regdate.month = 3;
63
regdate.day = 5;
64
regdate.hour = 14;
65
regdate.minute = 30;
66
numorders = 0;
67
68
if(mysql_stmt_execute(stmt) != 0)
69
{
70
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
71
return 1;
72
}
73
74
id = 2;
75
strcpy(name, "Homer Simpson");
76
regdate.year = 2019;
77
regdate.month = 4;
78
regdate.day = 12;
79
regdate.hour = 9;
80
regdate.minute = 15;
81
numorders = 0;
82
83
if(mysql_stmt_execute(stmt) != 0)
84
{
85
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
86
return 1;
87
}
88
89
mysql_close(conn);
90
return 0;
91
}
Итак, что вы думаете, лучше или хуже? Ну, одно преимущество заключается в том, что нам нужно разобрать оператор только один раз, чтобы в итоге он мог быть немного быстрее. Может быть. С другой стороны, если вы пишете какой-то фрагмент общего кода, который обрабатывает SQL-операторы, которые заранее не известны заранее, или, может быть, известны только их части, то это довольно неплохо.
Чтобы поддержать это, вы можете узнать, с какими параметрами вы обращаетесь с помощью вызова API после подготовки оператора. Подготовленный оператор API также обрабатывает операторы, которые возвращают данные, такие как SELECT , аналогичным образом. В общем, подготовленные операторы требуют немного больше кода в интерфейсе, но более функциональны.
Пример программы объяснил
Я буду держать полное описание того, как подготовленные операторы и соответствующий API работают, до следующего поста в блоге, но приведенная выше программа все еще нуждается в некотором объяснении.
После подключения к MariaDB с помощью обычной функции mysql_real_connect мы создаем дескриптор для работы с подготовленными операторами, а затем мы подготавливаем оператор SQL, который мы будем использовать позже, используя функцию mysql_stmt_prepare . Обратите внимание на обработку ошибок на этом этапе, и она повторяется везде, где вызывается подготовленная API-функция оператора, вместо вызова mysql_error , вы вызываете mysql_stmt_error, который принимает дескриптор оператора, а не дескриптор соединения, в качестве аргумента. SQL-операторы, которые мы готовим, имеют? чтобы указать, где мы должны привязать к параметрам.
После этого пришло время выполнить связывание, которое занимает большую часть кода. Привязка типа MYSQL_BIND имеет 4 члена, поскольку существует 4 параметра для привязки. Конечно, это может быть динамическим и размещаться в куче с использованием malloc или аналогичного, но в этом случае мы работаем с предопределенным оператором SQL и знаем, что существует 4 параметра.
Мы начинаем с обнуления всех членов по всем параметрам связывания. После этого мы заполняем только те элементы MYSQL_BIND, которые строго необходимы, и отмечаем, что мы используем разные типы для разных столбцов, чтобы соответствовать столбцам таблицы. В частности, столбец DATETIME, который сопоставлен со структурой MYSQL_TIME , но это не является строго необходимым, поскольку MariaDB предоставит необходимое преобразование, например, мы могли бы передать допустимую строку datetime для столбца cust_regdate . Затем мы осуществляем фактическое связывание, вызывая функцию mysql_stmt_bind_param .
Наконец, мы заполняем значения, к которым привязаны параметры, и также устанавливаем значения индикатора, все они нормальные, кроме значения для строки, для которого установлено значение STMT_INDICATOR_NTS, чтобы указать, что это строка с нулевым символом в конце . После этого мы вызываем mysql_stmt_execute для выполнения подготовленного оператора.
Массовая загрузка — подготовленные операторы с входными массивами
Если вы посмотрите на подготовленный код оператора выше, вы поймете, что если вы хотите вставить две или более строк за один раз, вы должны подготовить и выполнить что-то вроде этого:
Джава
xxxxxxxxxx
1
INSERT INTO customers VALUES(?, ?, ?, ?),(?, ?, ?, ?)
Чтобы это работало, вы должны связать 8 программных переменных, и это не кажется очень гибким, верно? Вам нужно будет подготовить другой оператор в зависимости от того, сколько строк вы вставляете, и это так же неуклюже, как когда вам приходится делать то же самое с текстовым интерфейсом. С MariaDB и использованием MariaDB Connector на самом деле есть лучший способ, который заключается в использовании привязки массива.
Это работает так, что каждая программная переменная связывания является массивом значений, и затем установите их правильно, сообщите MariaDB, насколько велик массив, и затем произвольное количество строк может быть вставлено одним оператором. Это, вероятно, лучше всего объяснить на примере, снова выполнив то же самое, что и в предыдущих примерах, но еще одним другим способом:
Джава
xxxxxxxxxx
1
#include <stdio.h>
2
#include <string.h>
3
#include <mysql.h>
4
5
int main(int argc, char *argv[])
6
{
7
MYSQL *conn;
8
MYSQL_STMT *stmt;
9
int id[2];
10
char id_ind[2];
11
char *nameptr[2];
12
char name[2][256];
13
char name_ind[2];
14
long name_len[2];
15
MYSQL_TIME *regdateptr[2];
16
MYSQL_TIME regdate[2];
17
char regdate_ind[2];
18
int numorders[2];
19
char numorders_ind[2];
20
MYSQL_BIND bind[4];
21
unsigned int numrows;
22
23
conn = mysql_init(NULL);
24
if(mysql_real_connect(conn, "localhost", "root", NULL, "blog", 3306,
25
"/var/lib/mysql/mysql.sock", CLIENT_INTERACTIVE) == NULL)
26
{
27
fprintf(stderr, "Error: %s\n", mysql_error(conn));
28
return 1;
29
}
30
31
stmt = mysql_stmt_init(conn);
32
if(mysql_stmt_prepare(stmt, "INSERT INTO customers VALUES(?, ?, ?, ?)", -1) != 0)
33
{
34
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
35
return 1;
36
}
37
38
memset(bind, 0, sizeof(bind));
39
bind[0].u.indicator = id_ind;
40
bind[0].buffer_type = MYSQL_TYPE_LONG;
41
bind[0].buffer = id;
42
bind[1].u.indicator = name_ind;
43
bind[1].buffer_type = MYSQL_TYPE_STRING;
44
bind[1].buffer = nameptr;
45
bind[1].length = name_len;
46
bind[2].u.indicator = regdate_ind;
47
bind[2].buffer_type = MYSQL_TYPE_DATETIME;
48
bind[2].buffer = regdateptr;
49
bind[3].u.indicator = numorders_ind;
50
bind[3].buffer_type = MYSQL_TYPE_LONG;
51
bind[3].buffer = numorders;
52
53
if(mysql_stmt_bind_param(stmt, bind) != 0)
54
{
55
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
56
return 1;
57
}
58
59
memset(®date, 0, sizeof(regdate));
60
61
id_ind[0] = regdate_ind[0] = numorders_ind[0] = STMT_INDICATOR_NONE;
62
name_ind[0] = STMT_INDICATOR_NTS;
63
id[0] = 1;
64
nameptr[0] = name[0];
65
strcpy(name[0], "Joe Bloggs");
66
name_len[0] = 256;
67
regdateptr[0] = ®date[0];
68
regdate[0].year = 2019;
69
regdate[0].month = 3;
70
regdate[0].day = 5;
71
regdate[0].hour = 14;
72
regdate[0].minute = 30;
73
numorders[0] = 0;
74
75
id_ind[1] = regdate_ind[1] = numorders_ind[1] = STMT_INDICATOR_NONE;
76
name_ind[1] = STMT_INDICATOR_NTS;
77
id[1] = 2;
78
nameptr[1] = name[1];
79
strcpy(name[1], "Homer Simpson");
80
name_len[1] = 256;
81
regdateptr[1] = ®date[1];
82
regdate[1].year = 2019;
83
regdate[1].month = 4;
84
regdate[1].day = 12;
85
regdate[1].hour = 9;
86
regdate[1].minute = 15;
87
numorders[1] = 0;
88
89
numrows = 2;
90
mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, &numrows);
91
if(mysql_stmt_execute(stmt) != 0)
92
{
93
fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
94
return 1;
95
}
96
97
mysql_close(conn);
98
return 0;
99
}
Здесь следует отметить пару ключевых моментов. Во-первых, когда мы связываем массив, любой тип данных, который является строкой char * или MYSQL_TIME, должен быть массивом указателей, и вы видите это в приведенном выше коде. Это делает этот код несколько сложным, но, в конце концов, это является преимуществом, поскольку связанные данные могут быть где угодно (например, каждая строка может быть членом класса или структуры где-то).
Во-вторых, чтобы сообщить MariaDB, что мы передаем массив, нам нужно вызвать mysql_stmt_attr_set и установить для атрибута STMT_ATTR_ARRAY_SIZE количество строк в массиве.
Пример программы объяснил
Приведенный выше пример не сильно отличается от первого подготовленного примера оператора, за некоторыми исключениями. Во-первых, процесс связывания теперь указывает на наши значения массива, у нас есть только 2 значения в массиве, но это все равно должно иллюстрировать мою точку зрения. А для столбцов и cust_regdate мы также делаем привязку к массиву указателей на фактические значения. Перед вызовом единственного mysql_stmt_execute нам также нужно указать MariaDB, сколько строк нужно вставить.
Заключение
Возможность загружать данные в MariaDB в виде программных массивов данных имеет ряд преимуществ, с которыми программно легче работать, чем с одной строкой массива, в частности, если последняя состоит из данных для многих строк. Выравнивание данных программы, содержащихся в классах или аналогичных, также проще, что обеспечивает лучшую интеграцию кода.
Наконец, производительность немного выше, особенно когда INSERT содержит много строк данных.
Счастливого SQL’а!
Дальнейшее чтение
Расписание резервного копирования для баз данных MySQL и MariaDB