Здравейте,

Захванах се да пиша една процедура в mySQL за създаване на дялове в таблиците (partitions). Възниква обаче един проблем, който не можах да разреша:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `rowCount`()
BEGIN
T_BLOCK: BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE t_name VARCHAR(255);
DECLARE table_list CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema='TEST' and engine!='InnoDB';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN table_list;
table_loop: LOOP
	FETCH table_list INTO t_name;
	IF done THEN
		LEAVE table_loop;
	END IF;
	D_BLOCK: BEGIN
		DECLARE data INT;
//ето тук възниква проблема - който и от двата вариант да използвам получавам грешка за награда
{ //1ви вариант
		DECLARE date_list CURSOR FOR SELECT TO_DAYS(DATE(CONCAT_WS("-",YEAR(a.Uhrzeit),MONTH(a.Uhrzeit),1))) AS days FROM (SELECT t_name) as a GROUP BY YEAR(a.Uhrzeit),MONTH(a.Uhrzeit);
}
{ //2ри вариант
		set @a=concat('DECLARE date_list CURSOR FOR SELECT TO_DAYS(DATE(CONCAT_WS("-",YEAR(Uhrzeit),MONTH(Uhrzeit),1))) AS days FROM ',(SELECT t_name),' GROUP BY YEAR(Uhrzeit),MONTH(Uhrzeit);');
		PREPARE stmt1 FROM @a;
		EXECUTE stmt1;
		DEALLOCATE PREPARE stmt1;
}
--		OPEN date_list;
--		data_loop: LOOP
--			FETCH date_list INTO data;
--			IF done THEN
--				SET done = FALSE;
--				LEAVE data_loop;
--			END IF;
--			select data,t_name;
--		END LOOP;
	END D_BLOCK;
END LOOP;
CLOSE table_list;
END T_BLOCK;
END

В кода съм вмъкнал коментар пред проблемната част. Както се вижда искам mySQL сам да си вади списък с таблиците в дадена БД и по отделно всяка една от таблиците да бъде променяна. Проблема възниква когато се опитам да дефинирам курсор. Ако използвам втория вариант получавам следната грешка:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE date_list CURSOR FOR SELECT TO_DAYS(DATE(CONCAT_WS("-",YEAR(Uhrzeit),MON' at line 1

Ако използвам първия вариант получавам следната грешка:

ERROR 1054 (42S22): Unknown column 'a.Uhrzeit' in 'field list'

Прегледах заявката, която декларира курсора и при нея всичко е наред когато я извадя от променливата:

DECLARE date_list CURSOR FOR SELECT TO_DAYS(DATE(CONCAT_WS("-",YEAR(Uhrzeit),MONTH(Uhrzeit),1))) AS days FROM table_1 GROUP BY YEAR(Uhrzeit),MONTH(Uhrzeit);
DECLARE date_list CURSOR FOR SELECT TO_DAYS(DATE(CONCAT_WS("-",YEAR(Uhrzeit),MONTH(Uhrzeit),1))) AS days FROM table_2 GROUP BY YEAR(Uhrzeit),MONTH(Uhrzeit);
DECLARE date_list CURSOR FOR SELECT TO_DAYS(DATE(CONCAT_WS("-",YEAR(Uhrzeit),MONTH(Uhrzeit),1))) AS days FROM table_3 GROUP BY YEAR(Uhrzeit),MONTH(Uhrzeit);

Това са 3те реда при изпълнението на основния курсор (3 са таблиците, които ме интересуват в момента в ДБ).

Не мога да разбера откъде произлизат въпросните грешки.

не твърдя че съм специалист по SQL, но това ми звучи доста неправилно

FROM (SELECT t_name) as a

може би трябва да е нещо такова

FROM t_name as a

t_name е променлива. Ако я оставя без SELECT, mySQL ще търси таблица с името на променливата, без да гледа какво е съдържанието й.

Редактирано 3 март 201214 г. от darkyto (преглед на промените)

t_name е променлива. Ако я оставя без SELECT, mySQL ще търси таблица с името на променливата, без да гледа какво е съдържанието й.

разбрах, но в нея има лист, а from очаква една (или повече) таблици и ако са повече трябва да са разделени със запетайка. Но тогава as има смисъл само за последната от списъка

П.П. И не е ли по-лесно партиционирането да е с диапазони в момента на създаването на таблицата. след това само местите данните от старата в новата

Не е лист. Променливата се задава с курсор, т.е. тя е стринг, в който се съдържа името на поредната таблица. table_list е списъка с имената на таблиците, но при loop на курсора от него се извежда само името на таблицата, където се намира курсора. При създаването на таблицата в нея няма да има информация. Освен това аз няма как да предвидя данните в таблицата от коя дата започват, т.е. може да има таблици с информация от 1.1.12, а може да има и такива с 1.1.11, което аз няма как да предвидя. Идеята е за всяка таблица да има дялове според година.месец, започвайки с нулев дял (т.е. с дял обхващащ интервала преди първия запис в таблицата) и всеки месец ще се добавя по 1 дял за съответния следващ месец.

Не е лист. Променливата се задава с курсор, т.е. тя е стринг, в който се съдържа името на поредната таблица. table_list е списъка с имената на таблиците, но при loop на курсора от него се извежда само името на таблицата, където се намира курсора.

При създаването на таблицата в нея няма да има информация. Освен това аз няма как да предвидя данните в таблицата от коя дата започват, т.е. може да има таблици с информация от 1.1.12, а може да има и такива с 1.1.11, което аз няма как да предвидя. Идеята е за всяка таблица да има дялове според година.месец, започвайки с нулев дял (т.е. с дял обхващащ интервала преди първия запис в таблицата) и всеки месец ще се добавя по 1 дял за съответния следващ месец.

това с правенето не мисля че е разумно, смятам че добрите практики отричат такова динамично променяне на структурата на базата. Нормално се правят при създаването, и какво по-просто да се създадат на ръка направо партициите за 12-те месеца. Мисля си че би било по-лесно да се създаде процедура, която да променя таблицата на 1 януари като прави нови 12 партиции за месеците за текущата година

За момента идеята е на всеки месец да се правят, но това, което предлагаш на база 1 година е интересно предложение и не е изключено да го приложа, но мисля, че схвана основната идея

За момента идеята е на всеки месец да се правят, но това, което предлагаш на база 1 година е интересно предложение и не е изключено да го приложа, но мисля, че схвана основната идея

Да, но сериозните DBA-и не биха били съгласни с идеята за динамично променяне на схемата. И мисля че едногодишна схема ще е напълно достатъчна. А за промяната - може и с някакъв прост скрипт (шел, SQL, perl), който може да вземе текущата година и да брои от 1 до 12

Проблема е, че в базата ще се създават динамично таблици без да е предвидимо кога точно ще се случва това. Искаше ми се така да направя процедурата/функцията, че като я поставя в "event scheduler" на mySQL да не ги мисля повече дяловете.

Проблема е, че в базата ще се създават динамично таблици без да е предвидимо кога точно ще се случва това. Искаше ми се така да направя процедурата/функцията, че като я поставя в "event scheduler" на mySQL да не ги мисля повече дяловете.

Ами няма да се създават динамично, а ще са статични, създадени в един момент. Ако са временни е приемливо да се създават динамично. Но такива в които да се пази информация....

Не искам да отмествам темата върху дизайна на базата, защото го мислих достатъчно, консултирал съм се и с по-кадърни от мен хора. Детайлите са много за да реша, че ми трябва точно такова решение за дяловете в таблиците.