Запуск запросов MySQL к разным базам данных

У нас есть система, в которой у нас есть база данных для каждого подразделения, в настоящее время у нас более 20 подразделений.

Поэтому, когда нам нужно обновить/удалить/изменить/создать новую таблицу, мы должны бросить все эти базы данных и выполнить запросы.

Иногда люди не следуют процедурам (всегда?), и в итоге у нас остаются структуры, которые не обновляются.

Я искал способ запускать одни и те же запросы ко всей базе данных без использования bash или внешних скриптов.

Итак, вот некоторые из вещей, которые я нашел:

CALL FOR EACH("SELECT databases WHERE `DATABASE` LIKE 'division_%'" , ${1});

где я мог бы ввести запрос в ${1}

или это (менее динамично):

call $('{a, b}'   , 'ALTER TABLE division_${1}.caching ADD COLUMN notes VARCHAR(4096) CHARSET utf8'');

Но это дает мне "База данных не выбрана"

Любая идея о том, как действовать в этой ситуации?


mysql dynamic-queries multiple-databases
person Tarek    schedule 26.06.2014    source источник
comment
Здесь бесполезный ответ, но на самом деле это должна быть одна и та же база данных, если они являются частью одной и той же системы. Немного более полезный ответ, если вы посмотрите на подготовленные операторы, они могут вам помочь ( dev.mysql.com/doc/refman/5.6/en/ )   -  person Simon at My School Portal    schedule 26.06.2014
comment
@Simonatmso.net Я согласен ... Мы получили этот прекрасный проект от другой компании ... В любом случае, у него есть плюсы и минусы (много записей 650k+ / table) .. Скоро опубликую решение, которое я нашел.   -  person Tarek    schedule 26.06.2014

Ответы (2)


arrow_upward
0
arrow_downward

Это решение, которое я нашел, и оно работает:

USE division_global;

DELIMITER $$

CREATE PROCEDURE `MultipleSchemaQuery`()
BEGIN
    declare scName varchar(250);
    declare q varchar(2000);

    DROP TABLE IF EXISTS ResultSet;
    create temporary table ResultSet (
     option_value varchar(200)
    );

    DROP TABLE IF EXISTS MySchemaNames;
    create temporary table MySchemaNames (
        schemaName varchar(250)
    );

    insert into MySchemaNames
    SELECT distinct
        TABLE_SCHEMA as SchemaName
    FROM 
        `information_schema`.`TABLES`  
    where 
         TABLE_SCHEMA LIKE 'division_%';

label1:
    LOOP
        set scName = (select schemaName from MySchemaNames limit 1);
        // The Query
        set @q = concat('TRUNCATE TABLE ', scName, '.caching');
        PREPARE stmt1 FROM @q;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;

        delete from MySchemaNames where schemaName = scName;
        IF ((select count(*) from MySchemaNames) > 0) THEN
            ITERATE label1;
        END IF;
        LEAVE label1;

    END LOOP label1;

    SELECT * FROM ResultSet;

    DROP TABLE IF EXISTS MySchemaNames;
    DROP TABLE IF EXISTS ResultSet;
END
$$

Вдохновленный этим:

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

person Tarek    schedule 26.06.2014

arrow_upward
0
arrow_downward

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

cat procedure.sql
DELIMITER $$

DROP PROCEDURE IF EXISTS alter_all $$

CREATE PROCEDURE alter_all()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE _schema VARCHAR(30);
  DECLARE cur CURSOR FOR select SCHEMA_NAME from information_schema.SCHEMATA where SCHEMA_NAME like 'division_%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := 1;

  OPEN cur;

  alterLoop: LOOP
    FETCH cur into _schema;
    if done = 1 THEN
      LEAVE alterLoop;
    END IF;
    SET @mystmt = concat('ALTER TABLE ', _schema, '.caching ADD COLUMN notes VARCHAR(4096)');
    PREPARE stmt3 FROM @mystmt;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
  END LOOP alterLoop;

  CLOSE cur;

END $$

DELIMITER ;

Итак, давайте попробуем (используя версию сервера: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)):

> create schema division_1 default character set 'UTF8';
> create table division_1.caching (id int not null auto_increment primary key, value varchar(10));
> create schema division_2 default character set 'UTF8';
> create table division_2.caching (id int not null auto_increment primary key, value varchar(10));
> use division_1;
> source procedure.sql
> CALL alter_all();
Query OK, 0 rows affected, 1 warning (0.05 sec)
> desc caching;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| value | varchar(10)   | YES  |     | NULL    |                |
| notes | varchar(4096) | YES  |     | NULL    |                |
+-------+---------------+------+-----+---------+----------------+
> desc division_2.caching
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| value | varchar(10)   | YES  |     | NULL    |                |
| notes | varchar(4096) | YES  |     | NULL    |                |
+-------+---------------+------+-----+---------+----------------+
person Pablo Carranza    schedule 05.07.2014
comment
Похоже на тот, который я добавил, но я хотел больше для нескольких запросов (иногда у нас есть более 100 запросов для запуска) - person Tarek; 05.07.2014
comment
Затем вы можете передать запросы для сборки в качестве параметров proc или сохранить запросы в таблице и загрузить оттуда вместо выполнения concat только для одного случая. - person Pablo Carranza; 05.07.2014