Ответ 1
Этот код довольно длинный. Я извиняюсь. Он состоит из двух хранимых процедур. Вы можете быть удовлетворены только первым. Второй использует вывод из первого (данные сначала слева в таблицах). Вы также можете комбинировать код в один. Но я держал их отдельно. Вторая сохраненная процедура производит вывод, похожий на describe myTable. Но он выполняет его для ВСЕХ таблиц в базе данных, которую вы хотите получить.
Вы используете это, передавая параметр (строка) для базы данных, в которую необходимо отчитываться.
Я создаю отдельную базу данных, и код явно ссылается на таблицы в этой базе данных по имени. Поэтому, если у вас есть привилегии EXECUTE для этой хранимой процедуры, вы можете запустить ее из любой текущей базы данных. Поэтому, как простой тест, не устанавливайте базу данных отчетов в качестве текущей базы данных и просто вызывайте хранимую процедуру по имени (с указанием имени db отчета). Все это показано в тестовом фрагменте ниже.
Две хранимые процедуры
CREATE SCHEMA Reporting101a; -- See **Note1**
DROP PROCEDURE IF EXISTS `Reporting101a`.`describeTables_v2a`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Reporting101a`.`describeTables_v2a`(
IN dbName varchar(100), -- the dbname to report table structures
OUT theSession int, -- OUT parameter for session# assigned
IN deleteSessionRows BOOL, -- true for delete rows when done from main reporting table for this session#
IN callTheSecondStoredProc BOOL -- TRUE = output is from Pretty output in Second Stored Proc. FALSE= not so pretty output
)
BEGIN
DECLARE thisTable CHAR(100);
DROP TEMPORARY TABLE IF EXISTS Reporting101a.tOutput;
CREATE TEMPORARY TABLE Reporting101a.tOutput
( id int auto_increment primary key,
tblName varchar(100) not null,
ordVal int not null,
cField varchar(100) not null,
cType varchar(100) not null,
cNull varchar(100) not null,
cKey varchar(100) not null,
cDefault varchar(100) null,
cExtra varchar(100) null
);
DROP TEMPORARY TABLE IF EXISTS Reporting101a.tOutput2;
CREATE TEMPORARY TABLE Reporting101a.tOutput2
( tblName varchar(100) primary key,
colCount int not null,
cFieldMaxLen int not null,
cTypeMaxLen int not null,
cNullMaxLen int not null,
cKeyMaxLen int not null,
cDefaultMaxLen int not null,
cExtraMaxLen int not null
);
INSERT Reporting101a.tOutput(tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra)
SELECT TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME AS Field, COLUMN_TYPE AS TYPE, RPAD(IS_NULLABLE,4,' ') AS 'Null',
RPAD(COLUMN_KEY,3,' ') AS 'Key',RPAD(COLUMN_DEFAULT,7,' ') AS 'DEFAULT',EXTRA AS Extra
FROM information_schema.columns WHERE table_schema = dbName ORDER BY table_name,ordinal_position;
-- select * from information_schema.columns WHERE table_schema = '57security' order by table_name,ordinal_position;
UPDATE Reporting101a.tOutput
SET cExtra=' '
WHERE cExtra='';
UPDATE Reporting101a.tOutput
SET cField=RPAD(cField,5,' ')
WHERE LENGTH(cField)<5;
INSERT Reporting101a.tOutput2(tblName,colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen)
SELECT tblName,COUNT(*),0,0,0,0,0,0
FROM Reporting101a.tOutput
GROUP BY tblName;
UPDATE tOutput2 t2
JOIN
( SELECT tblName,MAX(LENGTH(cField)) AS mField,MAX(LENGTH(cType)) AS mType,MAX(LENGTH(cNull)) AS mNull,
IFNULL(MAX(LENGTH(cKey)),0) AS mKey,IFNULL(MAX(LENGTH(cDefault)),0) AS mDefault,IFNULL(MAX(LENGTH(cExtra)),0) AS mExtra
FROM Reporting101a.tOutput
GROUP BY tblName
) x
ON x.tblName=t2.tblName
SET t2.cFieldMaxLen=x.mField,t2.cTypeMaxLen=x.mType,cNullMaxLen=x.mNull,
cKeyMaxLen=x.mKey,cDefaultMaxLen=x.mDefault,cExtraMaxLen=x.mExtra;
-- DROP TABLE Reporting101a.reportDataDefsSession; -- useful for quick change of structure of table
-- note, keep above drop call remmed out ! Just use it for quick tweaks to structure
CREATE TABLE IF NOT EXISTS Reporting101a.reportDataDefsSession
( -- for the sole purpose of safe session auto_inc usage
-- Please don't delete unless you want the sessions to experience aberant behavior
sessionId INT AUTO_INCREMENT PRIMARY KEY,
dummy CHAR(1) NOT NULL,
creationDT datetime not null
);
CREATE TABLE IF NOT EXISTS Reporting101a.reportDataDefs
( sessionId INT NOT NULL,
tblName VARCHAR(100) NOT NULL, -- Tablename
ordVal INT NOT NULL, -- the "position number" of the Column
cField VARCHAR(100) NOT NULL, -- The Column
cType VARCHAR(100) NOT NULL, -- Datatype
cNull VARCHAR(100) NOT NULL, -- Nullability
cKey VARCHAR(100) NOT NULL, -- Key info
cDefault VARCHAR(100) NULL, -- Default value
cExtra VARCHAR(100) NULL, -- Extra output
colCount INT NOT NULL, -- the columns here and below are de-normalize data
cFieldMaxLen INT NOT NULL,
cTypeMaxLen INT NOT NULL,
cNullMaxLen INT NOT NULL,
cKeyMaxLen INT NOT NULL,
cDefaultMaxLen INT NOT NULL,
cExtraMaxLen INT NOT NULL
);
-- For lack of a better notion, we are calling calls "sessions". The programmer calls the
-- First Stored Proc, and we call that a session after we get a unique next incrementing number.
-- That number is the session #. House all output with that as a column value. This allows us to
-- move between stored procs, have safe output, have historical snapshots, and retain the data
-- via a session # for later use, whatever use.
INSERT Reporting101a.reportDataDefsSession(dummy,creationDT) VALUES ('X',now());
SET @mySession=LAST_INSERT_ID(); -- there it is, our session # (read the above paragraph)
INSERT Reporting101a.reportDataDefs(sessionId,tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,
colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen)
SELECT @mySession,t1.tblName,t1.ordVal,t1.cField,t1.cType,t1.cNull,t1.cKey,t1.cDefault,t1.cExtra,
t2.colCount,t2.cFieldMaxLen,t2.cTypeMaxLen,t2.cNullMaxLen,t2.cKeyMaxLen,t2.cDefaultMaxLen,t2.cExtraMaxLen
FROM Reporting101a.tOutput t1
JOIN Reporting101a.tOutput2 t2
ON t2.tblName=t1.tblName
ORDER BY t1.tblName,t1.id;
DROP TEMPORARY TABLE Reporting101a.tOutput;
DROP TEMPORARY TABLE Reporting101a.tOutput2;
SET [email protected]; -- the OUT var that came in as a parameter
-- ***************************************************************************
-- ***************************************************************************
-- Label "Some_Sort_of_Output":
IF callTheSecondStoredProc=TRUE THEN
-- The caller says to call the second stored proc (for Pretty Printing)
-- This will generate output similar to `DESCRIBE myTable`
-- But remember, it will do it for EVERY table in referenced database
CALL Reporting101a.`Print_Tables_Like_Describe`(@mySession);
-- The above call just gave you output.
ELSE
-- The caller chose to not auto call the Pretty Printing second stored procedure.
-- Note, the caller can easily call it right after using the OUT parameter.
-- So our output will be a resultset of out reportDataDefs table for this session #
SELECT *
FROM Reporting101a.reportDataDefs
WHERE [email protected]
ORDER BY tblName,ordVal;
END IF;
-- ***************************************************************************
-- ***************************************************************************
IF deleteSessionRows=TRUE THEN
-- The caller says output rows are NOT needed at this point. Delete them.
-- Note, if this boolean comes in TRUE, you can't call Pretty Printing
-- second stored procedure with the session # because the data is gone.
--
-- Regardless, you are getting something back from "Some_Sort_of_Output" above.
DELETE FROM Reporting101a.reportDataDefs
WHERE [email protected];
END IF;
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS `Reporting101a`.`Print_Tables_Like_Describe`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Reporting101a`.`Print_Tables_Like_Describe`(
pSessionId INT
)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE curTable VARCHAR(100) DEFAULT '';
DECLARE bFirst BOOL DEFAULT TRUE;
DECLARE lv_tblName,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra VARCHAR(100);
DECLARE lv_ordVal,lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,lv_cDefaultMaxLen,lv_cExtraMaxLen INT;
DECLARE cur1 CURSOR FOR SELECT tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,
colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen
FROM Reporting101a.reportDataDefs
WHERE sessionId=pSessionId
ORDER BY tblName,ordVal;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Please note in the above, CURSOR stuff must come last else "Error 1337: Variable or condition decl aft curs"
CREATE TABLE IF NOT EXISTS Reporting101a.reportOutput
( lineNum INT AUTO_INCREMENT PRIMARY KEY,
sessionId INT NOT NULL,
lineOut varchar(100) NOT NULL
);
-- INSERT Reporting101a.reportOutput(sessionId,lineOut)
-- SELECT
-- SET curTable='';
DELETE FROM Reporting101a.reportOutput
WHERE sessionId=pSessionId;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO lv_tblName,lv_ordVal,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra,
lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,lv_cDefaultMaxLen,lv_cExtraMaxLen ;
IF done THEN
LEAVE read_loop;
END IF;
IF lv_tblName<>curTable THEN
IF bFirst=FALSE THEN
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,'';
ELSE
SET bFirst=FALSE;
END IF;
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,lv_tblName;
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,CONCAT('+-',
REPEAT('-',GREATEST(5,lv_cFieldMaxLen)), '-+-',
REPEAT('-',GREATEST(4,lv_cTypeMaxLen)), '-+-',
REPEAT('-',GREATEST(4,lv_cNullMaxLen)), '-+-',
REPEAT('-',GREATEST(3,lv_cKeyMaxLen)), '-+-',
REPEAT('-',GREATEST(7,lv_cDefaultMaxLen)), '-+-',
REPEAT('-',GREATEST(5,lv_cExtraMaxLen)), '-+');
SET @dashLineNumRow=LAST_INSERT_ID();
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,CONCAT('| ',
'Field',
REPEAT(' ',GREATEST(0,lv_cFieldMaxLen-5)), ' | ',
'Type',
REPEAT(' ',GREATEST(0,lv_cTypeMaxLen-4)), ' | ',
'Null',
REPEAT(' ',GREATEST(0,lv_cNullMaxLen-4)), ' | ',
'Key',
REPEAT(' ',GREATEST(0,lv_cKeyMaxLen-3)), ' | ',
'Default',
REPEAT(' ',GREATEST(0,lv_cDefaultMaxLen-7)), ' | ',
'Extra',
REPEAT(' ',GREATEST(0,lv_cExtraMaxLen-5)), ' |');
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,lineOut
FROM Reporting101a.reportOutput
WHERE [email protected];
-- SELECT * FROM Reporting101a.reportDataDefs WHERE sessionId=24;
SET curTable=lv_tblName;
END IF;
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,
CONCAT('| ',
COALESCE(lv_cField,''),
COALESCE(REPEAT(' ',GREATEST(0,lv_cFieldMaxLen-LENGTH(lv_cField))),''),' | ',
COALESCE(lv_cType,''),
COALESCE(REPEAT(' ',GREATEST(0,lv_cTypeMaxLen-LENGTH(lv_cType))),''),' | ',
COALESCE(lv_cNull,''),
COALESCE(REPEAT(' ',GREATEST(0,lv_cNullMaxLen-LENGTH(lv_cNull))),''),' | ',
COALESCE(lv_cKey,' '),
COALESCE(REPEAT(' ',GREATEST(0,lv_cKeyMaxLen-LENGTH(lv_cKey))),''),' | ',
COALESCE(lv_cDefault,' '),
COALESCE(REPEAT(' ',GREATEST(0,lv_cDefaultMaxLen-LENGTH(lv_cDefault))),''),' | ',
COALESCE(lv_cExtra,' '),
COALESCE(REPEAT(' ',GREATEST(0,lv_cExtraMaxLen-LENGTH(lv_cExtra))),''),' |');
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,lineOut
FROM Reporting101a.reportOutput
WHERE [email protected];
END LOOP;
CLOSE cur1;
select lineOut as '' from Reporting101a.reportOutput where sessionId=pSessionId order by lineNum;
END$$
DELIMITER ;
Test
Тест:
-- See **Note2**
SET @theOutVar =-1; -- A variable used as the OUT variable below
-- See **Note3**
-- Note: with `TRUE` as the 4th parameter, this is a one call deal. Meaning, you are done.
call Reporting101a.describeTables_v2a('stackoverflow',@theOutVar,false,true);
-- See **Note4**
-- Primarily used if the 4th parameter above is false
call Reporting101a.Print_Tables_Like_Describe(@theOutVar); -- loads data for prettier results in chunk format.
Выход
+--------------------------------------------------------------------------------------------+
| |
+--------------------------------------------------------------------------------------------+
| course |
| +------------+--------------+------+-----+---------+----------------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +------------+--------------+------+-----+---------+----------------+ |
| | courseId | int(11) | NO | PRI | | auto_increment | |
| +------------+--------------+------+-----+---------+----------------+ |
| | deptId | int(11) | NO | MUL | | | |
| +------------+--------------+------+-----+---------+----------------+ |
| | courseName | varchar(100) | NO | | | | |
| +------------+--------------+------+-----+---------+----------------+ |
| |
| dept |
| +----------+--------------+------+-----+---------+----------------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +----------+--------------+------+-----+---------+----------------+ |
| | deptId | int(11) | NO | PRI | | auto_increment | |
| +----------+--------------+------+-----+---------+----------------+ |
| | deptName | varchar(100) | NO | | | | |
| +----------+--------------+------+-----+---------+----------------+ |
| |
| scjunction |
| +------------+---------+------+-----+---------+----------------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +------------+---------+------+-----+---------+----------------+ |
| | id | int(11) | NO | PRI | | auto_increment | |
| +------------+---------+------+-----+---------+----------------+ |
| | studentId | int(11) | NO | MUL | | | |
| +------------+---------+------+-----+---------+----------------+ |
| | courseId | int(11) | NO | MUL | | | |
| +------------+---------+------+-----+---------+----------------+ |
| | term | int(11) | NO | | | | |
| +------------+---------+------+-----+---------+----------------+ |
| | attendance | int(11) | NO | | | | |
| +------------+---------+------+-----+---------+----------------+ |
| | grade | int(11) | NO | | | | |
| +------------+---------+------+-----+---------+----------------+ |
| |
| student |
| +-----------+--------------+------+-----+---------+----------------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +-----------+--------------+------+-----+---------+----------------+ |
| | studentId | int(11) | NO | PRI | | auto_increment | |
| +-----------+--------------+------+-----+---------+----------------+ |
| | fullName | varchar(100) | NO | | | | |
| +-----------+--------------+------+-----+---------+----------------+ |
| |
| testtable |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | noPKhere | int(11) | NO | | | | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | veryLongColumnName_And_Then.Some_%_More | decimal(12,2) | YES | | | | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | limit | int(11) | NO | | | | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| |
| testtable2 |
| +-------+---------+------+-----+---------+-------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +-------+---------+------+-----+---------+-------+ |
| | id | int(11) | NO | PRI | | | |
| +-------+---------+------+-----+---------+-------+ |
+--------------------------------------------------------------------------------------------+
Примечание1. Создана база данных под названием Reporting101a для размещения двух хранимых процедур и некоторых таблиц поддержки. Эти процедуры запускаются вызовом хранимой процедуры, ссылающейся на базу данных, о которой следует сообщать, с использованием строки.
Данные для получения выходных данных доступны через специальную базу данных INFORMATION_SCHEMA безопасным способом только для READ. Таким образом, сообщаемая база данных не затрагивается.
В этой базе данных хранятся три не временных таблицы.
-
reportDataDefsSession- простая таблица, используемая для получения сеанса # -
reportDataDefs- данные возвращаются изINFORMATION_SCHEMAи немного массируются. Он основан на сеансе. -
reportOutput- Таблица для печати, например MySQLDESCRIBE. Это всего лишь таблица для объединения результатов. Он основан на сеансе.
Примечание2. Эта переменная INT включается в качестве параметра параметра OUT, записывается в и позволяет вам вклинировать ваш другой код после первой хранимой процедуры, которая подготавливает данные. Он представляет сеанС#, который изолирует вывод для последующих отчетов.
В некоторых средах, таких как PHP, есть определенные трюки, которые делают это шоу-пробкой для некоторых программистов. Поэтому, если вам нужно объединить обе хранимые процедуры самостоятельно, сделайте это (или попросите меня отдельно, если вы запутались).
В любом случае, это показывает, как данные или усилия могут связывать хранимые процедуры с цепочкой.
Честно говоря, одна из основных причин, по которой я выхожу с сеансом # в качестве параметра OUT, - это то, что я знаю, что мне нужно создать CURSOR, чтобы получить красивый вывод. И это требует Cursor DECLARE в верхней части второй хранимой процедуры. И DECLARE должен находиться в верхней части хранимой процедуры. Таким образом, с привязанными руками, я пошел по этому маршруту.
Примечание3. Это вызов первой хранимой процедуры. Очень вероятно, что вы будете сделаны после того, как этот вызов будет иметь ИСТИННЫЙ как ваш 4-й параметр. Хранимая процедура хорошо документирована внутри нее. Третий параметр предназначен для того, хотите ли вы удалить данные из таблицы отчетов для сеанса #. Удаление происходит после любого вывода в виде набора результатов. Таким образом, это зависит от вашего выбора.
Параметры:
- имя базы данных для описания всех таблиц, таких как
describe myTable - параметр
INTOUTдля хранения сеанса # - boolean: вы хотите, чтобы данные были удалены из таблицы отчетов в конце.
- boolean: следует ли мы автоматически вызвать довольно печатную хранимую процедуру, которая генерирует вывод
DESCRIBE-like. Если вы решите передать параметр 4 какFALSE, то ваш вывод может выглядеть следующим образом:
Примечание4. Используется в тех случаях, когда вы хотите получить другой вывод, но хотите, чтобы сеанС# работал. Вам это обычно не нужно.