CONNECT MYSQL Table Type: ejemplo de acceso a tablas MySQL / MariaDB

Este tipo de tabla usa la API libmysql para acceder a una tabla o vista de MySQL o MariaDB. Esta tabla debe crearse en el servidor actual o en otro servidor local o remoto. Esto es similar a lo que proporciona el motor de almacenamiento FederatedX con algunas diferencias.

Actualmente, la sintaxis similar a la federada se puede utilizar para crear una tabla de este tipo, por ejemplo:

createtable essai (
  num integer(4)notnull,
  line char(15)notnull)engine=CONNECT table_type=MYSQL
connection='mysql://[email protected]/test/people';

La cadena de conexión puede tener la misma sintaxis que la utilizada por FEDERATED

scheme://username:[email protected]:port/database/tablename
scheme://[email protected]/database/tablename
scheme://username:[email protected]/database/tablename
scheme://username:[email protected]/database/tablename

Sin embargo, también se puede combinar con opciones estándar de conexión. Por ejemplo:

createtable essai (
  num integer(4)notnull,
  line char(15)notnull)engine=CONNECT table_type=MYSQL dbname=test tabname=people
connection='mysql://[email protected]';

También se puede especificar como referencia a un servidor federado:

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

También se acepta la sintaxis CONNECT pura (obsoleta):

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

Los elementos de conexión específicos son:

• – Cuando el host se especifica como “localhost”, la conexión se establece en Linux mediante sockets de Linux. En Windows, la conexión se establece de forma predeterminada utilizando memoria compartida si está habilitada. De lo contrario, se utiliza el protocolo TCP. Una alternativa es especificar el host como “.” para usar una conexión de tubería con nombre (si está habilitada). Esto hace posible utilizar estos tipos de tablas con el servidor que omite la red.

Precaución: ¡Tenga cuidado de no consultar la propia tabla MYSQL para evitar un bucle infinito!

La tabla MYSQL puede referirse tanto al servidor actual como a otro servidor. Las vistas pueden ser referidas por su nombre o dando directamente una definición de fuente, por ejemplo:

createtable grp engine=connect table_type=mysql
CONNECTION='mysql://[email protected]/test/people'
SRCDEF='select title, count(*) as cnt from employees group by title';

Cuando se especifican, las columnas de la tabla mysql deben existir en la tabla a la que se accede con el mismo nombre, pero pueden ser solo un subconjunto de ellas y especificarse en un orden diferente. Su tipo debe ser un tipo admitido por CONNECT y, si no es idéntico al tipo de la columna de coincidencia de la tabla a la que se accede, se puede realizar una conversión de acuerdo con las reglas dadas en Conversión de tipo de datos.

Nota: Para las columnas propensas a ser objeto de una cláusula where, mantenga el tipo de columna compatible con el tipo de columna de la tabla de origen (numérico o de caracteres) para tener una reformulación correcta de la cláusula where.

Si no desea restringir o cambiar la definición de columna, no la proporcione y deje que CONNECT obtenga la definición de columna del servidor remoto. Por ejemplo:

createtable essai engine=CONNECT table_type=MYSQL
connection='mysql://[email protected]/test/people';

Esto creará el essai tabla con las mismas columnas que la tabla de personas. Si la tabla de destino contiene columnas de tipo incompatible CONNECT, consulte Conversión de tipo de datos para saber cómo se pueden convertir u omitir estas columnas.

Especificación del juego de caracteres

Al acceder a la tabla remota, CONNECT establece el juego de caracteres de conexión en el juego de caracteres predeterminado de la tabla local como lo hace el motor FEDERATED.

No especifique un juego de caracteres de columna si es diferente del juego de caracteres predeterminado de la tabla, incluso cuando es el caso en la tabla remota. Esto se debe a que la columna remota se traduce al juego de caracteres de la tabla local al leerla. Este es el valor predeterminado, pero se puede modificar configurando la variable character_set_results del servidor de destino. Si debe mantener su configuración, por ejemplo en UTF8 cuando contiene caracteres Unicode, especifique el juego de caracteres predeterminado local para su juego de caracteres.

Esto significa que no es posible recuperar correctamente una tabla remota si contiene columnas con diferentes conjuntos de caracteres. Una solución es recuperarlo mediante varias tablas locales, cada una de las cuales accede solo a columnas con el mismo juego de caracteres.

Indexación de tablas MYSQL

Los índices rara vez son útiles con tablas MYSQL. Esto se debe a que CONNECT intenta acceder solo a las filas solicitadas. Por ejemplo, si preguntas:

select*from essai where num =23;

MariaDB hasta 10.1.1

Hasta 10.1.1, MariaDB requería que la variable optimizer_switch se estableciera globalmente de la siguiente manera: optimizer_switch=engine_condition_pushdown=on. De lo contrario, CONNECT no puede obtener la cláusula WHERE de la consulta.

CONNECT construirá y enviará al servidor la consulta:

SELECT num, line FROM people WHERE num =23

Si el gente la tabla está indexada en num, la indexación se utilizará en el servidor remoto. Esto, en todos los casos, limitará la cantidad de datos a recuperar en la red.

Sin embargo, se puede especificar un índice para las columnas que tienden a usarse para unir otra tabla a la tabla MYSQL. Por ejemplo:

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;

Si el identificación columna de la tabla remota dirigida por el cnc_tab La tabla MYSQL está indexada (lo cual es probable si es una clave) también debe indexar la identificación columna del MYSQL cnc_tab mesa. Si es así, usando la indexación “remota” como hace FEDERATED, solo las filas útiles de la tabla remota serán recuperadas durante el proceso de unión. Sin embargo, debido a que estas filas se recuperan mediante declaraciones SELECT independientes, esto será útil solo cuando se recuperen algunas filas de una tabla grande.

En particular, no debe especificar un índice para las columnas que no se usan para unirse y, sobre todo, NO indexe una columna unida si no está indexada en la tabla remota. Esto provocaría que varios escaneos de la tabla remota recuperen las filas unidas una por una.

Operaciones de modificación de datos

El tipo CONNECT MYSQL admite SELECT e INSERT y una forma algo limitada de UPDATE y DELETE. Estos se describen a continuación.

El tipo MYSQL usa métodos similares al tipo ODBC para implementar los comandos INSERT, UPDATE y DELETE. Consulte el capítulo ODBC para conocer las restricciones que les conciernen.

Para los comandos UPDATE y DELETE, hay menos restricciones porque el servidor remoto es un servidor MySQL, la sintaxis del comando siempre será aceptable para el servidor remoto.

Por ejemplo, puede utilizar libremente palabras clave como IGNORE o LOW_PRIORITY, así como funciones escalares en las cláusulas SET y WHERE.

Sin embargo, todavía existe un problema con las declaraciones de tablas múltiples. Supongamos que tienes un t1 tabla en el servidor remoto y desea ejecutar una consulta como:

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

Cuando se analiza localmente, tendrá errores si no t1 la tabla existe o si no tiene las columnas referenciadas. Cuando t1 no existe, puede solucionar este problema creando un maniquí local t1 mesa:

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

Esto hará feliz al analizador local y permitirá ejecutar el comando en el servidor remoto. Sin embargo, tenga en cuenta que tener una tabla MySQL local definida en el control remoto t1 La tabla no resuelve el problema a menos que también sean nombres t1 en la zona.

Es por eso que, para permitir que todos los tipos de comandos ejecutados por la fuente de datos sin ninguna restricción, CONNECT proporcione un subtipo de tabla MySQL específico que se describe ahora.

Enviar comandos a un servidor MariaDB

Esto se puede hacer como para las tablas ODBC o JDBC definiendo una tabla específica que se utilizará para enviar comandos y obtener el resultado de su ejecución.

createtable send (
  command varchar(128)notnull,warningsint(4)notnull flag=3,
  number int(5)notnull flag=1,
  message varchar(255) flag=2)engine=connect table_type=mysql
connection='mysql://[email protected]/database'
option_list='Execsrc=1,Maxerr=2';

Los puntos clave en esta declaración de creación son la opción EXECSRC y la definición de columna.

La opción EXECSRC indica que esta tabla se utilizará para enviar comandos al servidor MariaDB. La mayoría de los comandos enviados no devuelven el conjunto de resultados. Por lo tanto, las columnas de la tabla se utilizan para especificar el comando a ejecutar y obtener el resultado de la ejecución. El nombre de estas columnas se puede elegir arbitrariamente, su función proviene del valor FLAG:

¿Cómo usar esta tabla y especificar el comando para enviar? Ejecutando un comando como:

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

Esto enviará el comando especificado en la cláusula WHERE a la fuente de datos y devolverá el resultado de su ejecución. La sintaxis de la cláusula WHERE debe ser exactamente como se muestra arriba. Por ejemplo:

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

Este comando devuelve:

Enviar varios comandos en una llamada

Puede ser más rápido de ejecutar porque solo habrá una conexión para todos. Para enviar varios comandos en una llamada, use la siguiente sintaxis:

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

Cuando se envían varios comandos, la ejecución se detiene al final de ellos o después de un comando que está en error. Para continuar después de n errores, configure la opción maxerr =norte (0 por defecto) en la lista de opciones.

Nota 1: Es posible especificar la opción SRCDEF al crear una tabla EXECSRC. Será el comando enviado por defecto cuando no se especifica una cláusula WHERE.

Nota 2: Deben escaparse las barras invertidas dentro de los comandos. Las comillas simples deben escaparse si el comando se especifica entre comillas simples y las comillas dobles si se especifica entre comillas dobles.

Nota 3: Los comandos enviados se aplican en la base de datos especificada. Sin embargo, pueden abordar cualquier tabla dentro de esta base de datos.

Nota 4: Actualmente, todos los comandos se ejecutan en modo AUTOCOMMIT.

Recuperación de advertencias y notas

Si un comando enviado hace que se emitan advertencias, es inútil volver a enviar un comando “mostrar advertencias” porque el servidor MariaDB se abre y se cierra al enviar comandos. Por lo tanto, recibir advertencias requiere una forma específica (y complicada).

Para indicar que se debe agregar un texto de advertencia al resultado devuelto, debe enviar una consulta de múltiples comandos que contenga “pseudo” comandos que no se envían al servidor sino que la tabla EXECSRC los interpreta directamente. Estos “pseudo” comandos son:

Tenga en cuenta que deben escribirse (caso insensible) exactamente como arriba, sin “s” finales. Por ejemplo:

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');

Esto puede devolver algo como esto:

La ejecución continuó después del comando erróneo debido a la opción MAXERR. Normalmente esto habría detenido la ejecución.

Por supuesto, el último comando “seleccionar” es inútil aquí porque no puede devolver el contenido de la tabla. En su lugar, debería usarse otra tabla MYSQL sin la opción EXECSRC y con la definición de columna adecuada.

Limitaciones del motor de conexión

Tipos de datos

Hay una longitud máxima de key.index de 255 bytes. Es posible que pueda declarar la tabla sin un índice y confiar en el pushdown de la condición del motor y el esquema remoto.

No se pueden utilizar los siguientes tipos:

• POCO
• BINARIO
• TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
• TINYTEXT, MEDIUMTEXT, LONGTEXT
• ENUM
• COLOCAR
• Tipos de geometría

Nota: TEXTO está permitido. Sin embargo, el manejo depende de los valores dados a las variables de sistema connect_type_conv y connect_conv_size, y por defecto no se permite la conversión de columnas TEXT.

Limitaciones de SQL

Las siguientes consultas SQL no son compatibles

• SUSTITUIR EN
• INSERTAR … EN LA ACTUALIZACIÓN DE CLAVE DUPLICADA

CONECTAR MYSQL versus FEDERADO

El tipo de tabla CONNECT MYSQL no debe considerarse como un reemplazo del motor FEDERATED (X). El uso principal del tipo MYSQL es acceder a otras tablas del motor como si fueran tablas CONNECT. Esto era necesario al acceder a tablas desde algunos tipos de tablas CONNECT como TBL, XCOL, OCCUR o PIVOT que están diseñadas para acceder solo a tablas CONNECT. Cuando su tabla de destino no es una tabla CONNECT, estos tipos usan silenciosamente internamente una tabla MYSQL intermedia.

Sin embargo, hay casos en los que puede usar las tablas MYSQL CONNECT usted mismo, por ejemplo:

1. Cuándo la mesa será utilizada por una mesa TBL. Esto le permite especificar los parámetros de conexión para cada subtabla y es más eficiente que usar una subtabla FEDERADA local.
2. Cuando los datos devueltos deseados se especifican directamente mediante la opción SRCDEF. Esto es excelente para permitir que el servidor remoto haga la mayor parte del trabajo, como agrupar y / o unir tablas. Esto no se puede hacer con el motor FEDERATED.
3. Para aprovechar la push_cond facilidad que agrega una cláusula where al comando enviado a la tabla remota. Esto restringe el tamaño del conjunto de resultados y puede ser crucial para tablas grandes.
4. Para tablas con la opción EXECSRC activada.
5. Al hacer pruebas. Por ejemplo, para comprobar una cadena de conexión.

Si necesita actualización, eliminación o inserción masiva de varias tablas en una tabla remota, puede utilizar alternativamente el motor FEDERADO o una tabla de “envío” especificando la opción EXECSRC activada.

Ver también

• Usando los tipos TBL y MYSQL juntos

El contenido reproducido en este sitio es propiedad de sus respectivos dueños, y MariaDB no revisa este contenido con anticipación. Los puntos de vista, la información y las opiniones expresadas por este contenido no representan necesariamente las de MariaDB o de cualquier otra parte.