Как получить параметры колонки при выборки данных через SELECT?

Question

[Роман Башарин]

Ребят, привет. Нигде не нашел ответа на вопрос. Но реализовать задачу нужно в любом случае, если не нативными для MySQL запросами, то через pdo/php.

При запросе SELECT order_id FROM orders WHERE order_id = 113 (условно), нужно получить не только value, но и свойства колонки, например так

'orders.order_id' => [
    'value' => '113',    
    'type' => 'int',
    'length' => '11'
]

'orders.order_id' решается через
PDO::ATTR_FETCH_TABLE_NAMES

А как можно получить все остальное? Пусть даже параметры кроме значения будут в виде 'params' => 'int(11) unsigned primary'. Лишь бы это было в одном массиве вместе с результатами select

Answer 1

[Евгений]

Использовать view information_schema:

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE table_name = 'tbl_name' AND COLUMN_NAME = 'col_name';

Comments

[Роман Башарин]

Вы не поняли вопроса. Я знаю как получить данные столбцов. Я знаю как сделать отдельно запрос SELECT. Я хочу узнать, есть ли готовая реализация, когда при SELECT * FROM fruits, мне возвращается не только массив значений, но и информация по каждому полю, как я привел в примере. Если нужно писать функцию на php которая будет сливать "2 массива в один" я знаю как это сделать. Но будет намного быстрее, если существует уже готовая нативная функция в MySQL или PDO

[Евгений]

Роман: Хорошо. Тогда посмотрите вот это: www.php.net/manual/en/pdostatement.getcolumnmeta.php

[Роман Башарин]

Евгений: Спасибо за наводку, но это не подходящий вариант. На него много ругаются, что метод с багами. Лично у меня он часть информации нулями выводит, например мах длинну строки. Видимо что-то с драйверами. На данный момент рассматриваю 2 варианта. array_merge_recursive приходящего массива от SELECT и массива хранящегося в файле, в котором есть параметры всех полей (файл заранее подготовлен, как выгрузка из базы). Второй вариант, при каждом запросе делать join со schema хотя это наверное извращение и будет медленней первого варианта.

[Евгений]

Роман: а почему бы не определить кастомную View, где и сделаете join? Я изначально с этой целью и ответил про information_schema. В моем случае сделал бы так, дабы не перетягивать логику выборку данных в код.

[Евгений]

Роман: просто файл это костыли. Если вы сделаете сервис, который будет обновлять этот файл при изменениях в БД - то еще куда бы ни шло. Даже так это будет велосипед, ИМХО, не самый удачный, особенно для реального проекта. Да и несчастный этот Join для современной БД - вообще не проблема.

[Роман Башарин]

Сейчас схема такая
1. Есть отдельная таблица в которой для каждого поля в этой БД созана уникальная запись
через SHOW TABLES -> SHOW COLUMNS FROM
2. После внесения изменений в структуру БД эта таблица обновляется (пока вручную)
3. Далее делается полный SELECT из неё и сериализованный массив пишется в файл
4. Во всех местах где нужно получить этот массив делается такой запрос
$templator = unserialize(file(TEMP_DIR.'cache/templator.txt')[0]);
5. Ну и далее по циклу для получения всей доп инфы
foreach ($data as $k => $v) {
if (isset($templator[$k])) {
$data[$k] = $templator[$k];
$data[$k]['value'] = $v;
}
}
В итоге получается что-то такое (на самом деле в массиве больше данных, но сейчас это не так важно)
'orders.order_id' => [
'value' => '113',
'type' => 'int',
'length' => '11'
]
6. Замерял скорость вывода массива из файла (код привел выше) vs каждый раз запроса массива из БД по всей таблице (которая сериализована в файле). Разница по скорости в 200 раз различается в пользу чтения из файла. (бд находится тут же на VDS'ке).
7. Сам не люблю костыли, поэтому и задал изначально вопрос, есть ли расширение для PHP или нативная функция в PDO которые позволяют это всё сделать одним запросом.
8. Как сделать join я так и не понял, но не уверен, что это будет быстрее чем моя реализация.
Вот например по такому запросу если делать join
SELECT "тут 15 полей" FROM "тут JOIN на 4 таблицы" и это не по 1 значению, а например 300 строк.
То есть 300 строк на 15 полей, это нужно 4500 join'ов (нам же по каждому полю нужно получить информацию).

Answer 2

[Immortal_pony]

Получение информации в общем виде:

SELECT `COLUMN_TYPE`
FROM `information_schema`.`COLUMNS`
WHERE 1
AND `TABLE_SCHEMA`='schema.name' -- не знаю вашей, не забудьте поменять
AND `TABLE_NAME`='orders'
AND `COLUMN_NAME`='order_id'

Получение информации и присоединение ее к каждой строке:

SELECT 
	`orders`.`order_id`,
	`information_column_order_id`.`COLUMN_TYPE` AS 'order_id_column_type'
FROM 
	`orders`
	JOIN `information_schema`.`COLUMNS` AS `information_column_order_id` ON (
		 `TABLE_SCHEMA`='schema.name' -- не знаю вашей, не забудьте поменять
		AND `TABLE_NAME`='orders'
		AND `COLUMN_NAME`='order_id'
	)

Comments

[Роман Башарин]

Вы не поняли вопроса. Я знаю как получить данные столбцов. Я знаю как сделать отдельно запрос SELECT. Я хочу узнать, есть ли готовая реализация, когда при SELECT * FROM fruits, мне возвращается не только массив значений, но и информация по каждому полю, как я привел в примере. Если нужно писать функцию на php которая будет сливать "2 массива в один" я знаю как это сделать. Но будет намного быстрее, если существует уже готовая нативная функция в MySQL или PDO

[Immortal_pony]

Роман, я указал вам на способ получения необходимой информации.
Присоединить эту информацию к каждой строке выборки можно, например, так:

SELECT 
	`orders`.`order_id`,
	`information_column_order_id`.`COLUMN_TYPE` AS 'order_id_column_type'
FROM 
	`orders`
	JOIN `information_schema`.`COLUMNS` AS `information_column_order_id` ON (
		 `TABLE_SCHEMA`='schema.name' -- не знаю вашей, не забудьте поменять
		AND `TABLE_NAME`='orders'
		AND `COLUMN_NAME`='order_id'
	)

[Роман Башарин]

Immortal_pony: понял почему сразу не подошел этот вариант. Объект PDO создает подключение к одной БД, а information_schema это другая БД. Как вы считаете. Что будет правильнее:
1) При каждому запросе создавать подключение к двум БД, делать выборку, а потом сливать массивы средствами PHP.
2) Перенести SELECT * FROM information_schema WHERE TABLE_SCHEMA = 'myDatabaseName' в мою базу данных. В таком случае будет один запрос к БД, JOIN средствами MySQL, что явно быстрее должно быть, чем слияние массивов в цикле средствами PHP.

Второй вариант на мой взгляд правильней, но я не знаю как автоматизировать обновление этой "информационной таблицы", в случае изменения структуры базы данных. p.s. В этой таблице есть ещё пара колонок, поэтому начисто переписывать таблицу не вариант. Например, есть колонка "русское название поля".

Вся эта история нужна для этого (псевдокод)
echo '
Это позволяет на клиенте лучше понимать какой тип поля, что в него можно писать, что нельзя, какая максимальная длинна и т.д.

[Immortal_pony]

Роман, information_schema - это не "другая БД", а другая схема БД. Вы можете составить запрос, в котором используется неограниченное количество таблиц из неограниченного числа схем.

Объект PDO создает подключение к одной БД

Это означает, что если вы опустите схему в синтаксисе запроса, то по умолчанию поиск таблицы будет производится в "подключенной" схеме. Однако, если вы будете указывать схему явно, то никто не мешает вам вытаскивать данные из неограниченного числа схем. Только убедитесь, что у пользователя есть необходимые права

[Роман Башарин]

Не могу сообразить. information_schema лежит на одном уровне с моей БД, если я открываю GUI для управления сервером MySQL. Когда я создаю объект PDO я явно указываю к какой БД подключиться. Вы имеете в виду, что я могу создав PDO объект для myDbName, указать в запросе "SELECT name FROM anotherDbName.users" получить из неё результат (при условии, что хватает прав)?

[Immortal_pony]

Роман,

Вы имеете в виду, что я могу создав PDO объект для myDbName, указать в запросе "SELECT name FROM anotherDbName.users" получить из неё результат (при условии, что хватает прав)?

Да.

Answer 3

[Melkij]

Приджойнить подходящую вьюху из INFORMATION_SCHEMA. Вроде бы INFORMATION_SCHEMA.COLUMNS должна содержать нужные данные.

Comments

[Роман Башарин]

Частично вы правы. Данная реализация может подойти. Обсуждение продолжу в другой ветке.

Answer 4

[FanatPHP]

Сколько ответов, и ни одного правильного

PDOStatement::getColumnMeta

Comments

[Роман Башарин]

Можете подсказать как это объединить с SELECT запросом? Сколько ни пробовал этот вариант ничего не получается. Например, для такого запроса
'SELECT uName, uSurname, uPhone, uMail FROM users INNER JOIN contacts ON users.user_id = contacts.user_id WHERE uSurname = 'Петров' ORDER BY user_id ASC'
В данном случае нужно получить мета информацию по 4 полям и заджойнить её с пришедшей информацией из SELECT. В селекте вернется несколько юзеров у которых фамилия "Петров".

[Immortal_pony]

FanatPHP, вас не смущают предупреждения в документации?

Warning
This function is EXPERIMENTAL. The behaviour of this function, its name, and surrounding documentation may change without notice in a future release of PHP. This function should be used at your own risk.

Warning
Warning
Not all PDO drivers support PDOStatement::getColumnMeta().

[Роман Башарин]

Immortal_pony: Ха ха. У меня она работает, но кривовато. Некоторые значения в массиве пустые