Подключение к MySQL тип таблицы: доступ к таблицам MySQL/MariaDB

Этот тип таблиц использует API libmysql для доступа к таблице или представлению MySQL или MariaDB. Таблица должна быть создана на текущем сервере или на другом локальном или удаленном сервере. Это аналогично тому, что предоставляет движок хранения FederatedX, с некоторыми отличиями.

В настоящее время для создания такой таблицы может использоваться синтаксис, похожий на Federated, например:

create table essai (
  num integer(4) not null,
  line char(15) not null)
engine=CONNECT table_type=MYSQL
connection='mysql://root@localhost/test/people';

Строка подключения может иметь такой же синтаксис, как и используемый для FEDERATED

scheme://username:password@hostname:port/database/tablename
scheme://username@hostname/database/tablename
scheme://username:password@hostname/database/tablename
scheme://username:password@hostname/database/tablename

Однако, она также может быть смешана со стандартными параметрами подключения. Например:

create table essai (
  num integer(4) not null,
  line char(15) not null)
engine=CONNECT table_type=MYSQL dbname=test tabname=people
connection='mysql://root@localhost';

Также можно указать ссылку на удаленный сервер:

connection="connection_one"
connection="connection_one/table_foo"

Принят также чистый (устаревший) синтаксис CONNECT:

create table essai (
  num integer(4) not null,
  line char(15) not null)
engine=CONNECT table_type=MYSQL dbname=test tabname=people
option_list='user=root,host=localhost';

Конкретные элементы подключения:

• - Когда хост задан как “localhost”, подключение устанавливается на Linux с использованием сокетов Linux. На Windows подключение по умолчанию устанавливается с использованием общей памяти, если она включена. В противном случае используется протокол TCP. Альтернативой является указание хоста как “.” для использования соединения с именованной пайп (если оно включено). Это позволяет использовать эти типы таблиц с сервером, минуя сетевое взаимодействие.

Таблица MYSQL может ссылаться на текущий сервер, а также на другой сервер. Представления могут быть указаны по имени или непосредственно путем предоставления определения источника, например:

create table grp engine=connect table_type=mysql
CONNECTION='mysql://root@localhost/test/people'
SRCDEF='select title, count(*) as cnt from employees group by title';

Если указано, столбцы таблицы mysql должны существовать в доступной таблице с тем же именем, но могут быть только подмножеством из них и заданы в другом порядке. Их тип должен поддерживаться CONNECT и, если он не идентичен типу соответствующего столбца в доступной таблице, преобразование может быть выполнено в соответствии с правилами, приведенными в Преобразовании типов данных.

Примечание: Для столбцов, которые могут быть целевыми в предложении WHERE, сохраняйте совместимость типа столбца с типом столбца исходной таблицы (числовой или символьный), чтобы обеспечить правильное переформулирование предложения WHERE.

Если вы не хотите ограничивать или изменять определение столбца, не предоставляйте его и позвольте CONNECT получить определение столбца с удаленного сервера. Например:

create table essai engine=CONNECT table_type=MYSQL
connection='mysql://root@localhost/test/people';

Это создаст таблицу essai со столбцами, такими же, как и в таблице people. Если целевая таблица содержит столбцы, несовместимые с CONNECT, см. Преобразование типов данных, чтобы узнать, как эти столбцы можно преобразовать или пропустить.

Указание кодовой страницы

При доступе к удаленной таблице CONNECT устанавливает кодовую страницу подключения по умолчанию локальной таблицы, как и движок FEDERATED.

Не указывайте кодовую страницу столбца, если она отличается от кодовой страницы таблицы по умолчанию, даже если это имеет место в удаленной таблице. Это происходит потому, что удаленный столбец преобразуется в локальную кодовую страницу таблицы при чтении. Это значение по умолчанию, но может быть изменено путем установки переменной character_set_results целевого сервера. Если необходимо сохранить ее значение, например, UTF8 при содержании символов Юникода, укажите локальную кодовую страницу по умолчанию как её кодовую страницу.

Это означает, что невозможно правильно извлечь удаленную таблицу, если она содержит столбцы с разными кодовыми страницами. Решение заключается в извлечении её через несколько локальных таблиц, каждая из которых получает доступ только к столбцам с одинаковой кодовой страницей.

Индексирование таблиц MYSQL

Индексы редко полезны с таблицами MYSQL. Это происходит потому, что CONNECT пытается получить доступ только к требуемым строкам. Например, если вы запросите:

select * from essai where num = 23;

CONNECT сгенерирует и отправит серверу запрос:

SELECT num, line FROM people WHERE num = 23

Если таблица people индексирована по num, индексация будет использоваться на удалённом сервере. Это в любом случае ограничит количество данных, которые нужно получить по сети.

Однако, индекс можно указать для столбцов, которые могут использоваться для соединения другой таблицы с таблицей MYSQL. Например:

select d.id, d.name, f.dept, f.salary
from loc_tab d straight_join cnc_tab f on d.id = f.id
where f.salary > 10000;

Если столбец id удалённой таблицы, на которую ссылается таблица MYSQL cnc_tab, индексирован (что вероятно, если это ключ), вам также следует индексировать столбец id таблицы MYSQL cnc_tab. В этом случае, используя «удалённую» индексацию, как это делает FEDERATED, только полезные строки удалённой таблицы будут извлечены во время процесса объединения. Однако, поскольку эти строки извлекаются с помощью отдельных запросов SELECT, это будет полезно только при извлечении нескольких строк из большой таблицы.

В частности, не следует указывать индекс для столбцов, не используемых для объединения, и, прежде всего, НЕ индексируйте объединённый столбец, если он не индексирован в удалённой таблице. Это приведёт к множественным сканированиям удалённой таблицы для извлечения строк объединения по одной.

Операции изменения данных

Тип CONNECT MYSQL поддерживает SELECT и INSERT, а также несколько ограниченную форму UPDATE и DELETE. Они описаны ниже.

Тип MYSQL использует аналогичные методы, чем тип ODBC, для реализации команд INSERT, UPDATE и DELETE. Обратитесь к главе ODBC для ограничений, касающихся их.

Для команд UPDATE и DELETE ограничений меньше, потому что удалённый сервер — сервер MySQL, поэтому синтаксис команды всегда будет приемлемым для удалённого сервера.

Например, вы можете свободно использовать ключевые слова, такие как IGNORE или LOW_PRIORITY, а также скалярные функции в предложениях SET и WHERE.

Однако, по-прежнему существует проблема с многотабличными операторами. Предположим, у вас есть таблица t1 на удалённом сервере и вы хотите выполнить запрос, такой как:

update essai as x set line = (select msg from t1 where id = x.num)
where num = 2;

При локальном разборе у вас будут ошибки, если таблица t1 не существует или если она не содержит указанные столбцы. Если t1 не существует, вы можете решить эту проблему, создав локальную подставную таблицу t1:

create table t1 (id int, msg char(1)) engine=BLACKHOLE;

Это сделает локальный анализатор довольным и позволит выполнить команду на удалённом сервере. Однако имейте в виду, что наличие локальной таблицы MySQL, определённой на удалённой таблице t1, не решает проблему, если только она не называется t1 и локально.

Вот почему, чтобы разрешить выполнение всех типов команд источником данных без ограничений, CONNECT предоставляет специфичный подтип таблицы MySQL, который описан ниже.

Отправка команд на сервер MariaDB

Это можно сделать, как для таблиц ODBC или JDBC, определив специфическую таблицу, которая будет использоваться для отправки команд и получения результата их выполнения.

create table send (
  command varchar(128) not null,
  warnings int(4) not null flag=3,
  number int(5) not null flag=1,
  message varchar(255) flag=2)
engine=connect table_type=mysql
connection='mysql://user@host/database'
option_list='Execsrc=1,Maxerr=2';

Ключевыми моментами в этом операторе CREATE являются опция EXECSRC и определение столбцов.

Опция EXECSRC указывает, что эта таблица будет использоваться для отправки команд на сервер MariaDB. Большинство отправленных команд не возвращают набор результатов. Поэтому столбцы таблицы используются для указания выполняемой команды и получения результата выполнения. Имена этих столбцов могут быть выбраны произвольно, их функция определяется значением FLAG:

Как использовать эту таблицу и указать команду для отправки? Выполнив команду, такую как:

select * from send where command = 'a command';

Это отправит команду, указанную в предложении WHERE, в источник данных и вернёт результат её выполнения. Синтаксис предложения WHERE должен быть точно таким, как показано выше. Например:

select * from send where command =
'CREATE TABLE people (
num integer(4) primary key autoincrement,
line char(15) not null';

Эта команда возвращает:

Отправка нескольких команд в одном вызове

Это может быть быстрее, поскольку будет только одно подключение для всех команд. Чтобы отправить несколько команд в одном вызове, используйте следующий синтаксис:

select * from send where command in (
"update people set line = 'Two' where id = 2",
"update people set line = 'Three' where id = 3");

При отправке нескольких команд выполнение останавливается в конце их выполнения или после команды, содержащей ошибку. Чтобы продолжить после n ошибок, установите опцию maxerr=n (по умолчанию 0) в списке параметров.

Примечание 1: Можно указать опцию SRCDEF при создании таблицы EXECSRC. Она будет по умолчанию отправлена командой, когда предложение WHERE не указано.

Примечание 2: Обратные слэши внутри команд должны быть экранированы. Одиночные кавычки должны быть экранированы, если команда указана в одиночных кавычках, и двойные кавычки — если в двойных.

Примечание 3: Отправленные команды применяются в указанной базе данных. Однако они могут адресовать любую таблицу в этой базе данных.

Примечание 4: В настоящее время все команды выполняются в режиме AUTOCOMMIT.

Получение предупреждений и заметок

Если отправленная команда вызывает предупреждения, бесполезно повторно отправлять команду «show warnings», так как сервер MariaDB открывается и закрывается при отправке команд. Поэтому получение предупреждений требует специфического (и тонкого) способа.

Чтобы указать, что текст предупреждений должен быть добавлен в возвращаемый результат, вы должны отправить многокомандный запрос, содержащий «псевдокоманды», которые не отправляются на сервер, а интерпретируются таблицей EXECSRC. Эти «псевдокоманды»:

Обратите внимание, что они должны быть написаны (регистр не учитывается) точно так, как выше, без конечного «s». Например:

select * from send where command in ('Warning','Note',
'drop table if exists try',
'create table try (id int key auto_increment, msg varchar(32) not
null) engine=aria',
"insert into try(msg) values('One'),(NULL),('Three') ",
"insert into try values(2,'Deux') on duplicate key update msg =
'Two'",
"insert into try(message) values('Four'),('Five'),('Six')",
'insert into try(id) values(NULL)',
"update try set msg = 'Four' where id = 4",
'select * from try');

Это может вернуть что-то вроде этого:

Выполнение продолжилось после команды с ошибкой из-за опции MAXERR. Обычно это остановило бы выполнение.

Конечно, последняя команда «select» бесполезна здесь, потому что она не может вернуть содержащую таблицу. Вместо этого следует использовать другую таблицу MYSQL без опции EXECSRC и с правильным определением столбцов.

Ограничения движка подключения

Типы данных

Максимальная длина ключа.индекса составляет 255 байт. Возможно, вы сможете объявить таблицу без индекса и положиться на механизм обработки условия pushdown и удалённую схему.

Следующие типы данных не могут быть использованы:

• BIT
• BINARY
• TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
• TINYTEXT, MEDIUMTEXT, LONGTEXT
• ENUM
• SET
• Типы геометрии

Примечание: TEXT разрешён. Однако обработка зависит от значений, заданных системным переменным connect_type_conv и connect_conv_size, и по умолчанию преобразование столбцов TEXT запрещено.

Ограничения SQL

Следующие SQL-запросы не поддерживаются

• REPLACE INTO
• INSERT ... ON DUPLICATE KEY UPDATE

CONNECT MYSQL по сравнению с FEDERATED

Тип таблицы CONNECT MYSQL не следует рассматривать как замену движку FEDERATED(X). Основное назначение типа MYSQL — доступ к таблицам других движков как к таблицам CONNECT. Это было необходимо при доступе к таблицам из некоторых типов таблиц CONNECT, таких как TBL, XCOL, OCCUR или PIVOT, которые предназначены только для доступа к таблицам CONNECT. Когда целевая таблица не является таблицей CONNECT, эти типы молча используют внутреннюю промежуточную таблицу MYSQL.

Однако существуют случаи, когда вы можете использовать таблицы MYSQL CONNECT:

1. Когда таблица будет использоваться таблицей TBL. Это позволяет указать параметры подключения для каждой подтаблицы и более эффективно, чем использование локальной подтаблицы FEDERATED.
2. Когда желаемые возвращаемые данные непосредственно задаются опцией SRCDEF. Это отлично подходит для того, чтобы позволить удалённому серверу выполнить большую часть работы, например, группирование и/или объединение таблиц. Это нельзя сделать с движком FEDERATED.
3. Для использования возможности push_cond, которая добавляет условие where к команде, отправленной в удалённую таблицу. Это ограничивает размер набора результатов и может быть критически важным для больших таблиц.
4. Для таблиц с включённой опцией EXECSRC.
5. При проведении тестов. Например, для проверки строки подключения.

Если вам нужна многотабличная обновление, удаление или массовая вставка в удалённую таблицу, вы можете альтернативно использовать движок FEDERATED или таблицу «send», указав опцию EXECSRC.

См. также

• Использование типов TBL и MYSQL вместе