SQL select join: можно ли префиксировать все столбцы как 'префикс.* ' ?

Кажется, ответ на ваш вопрос нет, однако один хак вы можете использовать, чтобы назначить фиктивный столбец для разделения каждой новой таблицы. Это особенно хорошо работает, если вы просматриваете результирующий набор для списка столбцов на языке сценариев, таком как Python или PHP.

SELECT '' as table1_dummy, table1.*, '' as table2_dummy, table2.*, '' as table3_dummy, table3.* FROM table1
JOIN table2 ON table2.table1id = table1.id
JOIN table3 ON table3.table1id = table1.id

Я понимаю, что это не отвечает на ваш вопрос точно, но если вы кодер, это отличный способ разделить таблицы с повторяющимися именами столбцов. Надеюсь, это кому-то поможет.

Я полностью понимаю, почему это необходимо - по крайней мере, для меня это удобно во время быстрого прототипирования, когда есть много таблиц, необходимых для объединения, в том числе много внутренних соединений. Как только имя столбца будет одинаковым через секунду "joinedtable.* "поле wild card, значения полей основной таблицы переопределяются значениями joinedtable. Ошибки подвержены, расстраивает и нарушение сухой при необходимости вручную указать поля таблицы с псевдонимами снова и снова...

здесь PHP (Wordpress) функция для достижения этой цели путем генерации кода вместе с примером того, как его использовать. В Примере он используется для быстрого создания пользовательского запроса, который предоставит поля связанного сообщения wordpress, на которое ссылались через расширенный пользовательские поля

единственная база данных, которую я знаю, это SQLite, в зависимости от настроек, которые вы настраиваете с PRAGMA full_column_names и PRAGMA short_column_names. См.http://www.sqlite.org/pragma.html

В противном случае все, что я могу рекомендовать, это выбрать столбцы в результирующем наборе по порядковому номеру, а не по имени столбца, если вам слишком сложно ввести имена столбцов в вашем запросе.

Это хороший пример того, почему это плохая практика, чтобы использовать SELECT * -- потому что в конце концов вам все равно придется вводить все имена столбцов.

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

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

Я нахожусь в той же лодке, что и OP - у меня есть десятки полей из 3 разных таблиц, к которым я присоединяюсь, некоторые из которых имеют одно и то же имя(т. е. ID, имя и т. д.). Я не хочу перечислять каждое поле, поэтому мое решение состояло в том, чтобы псевдонимить те поля, которые имеют общее имя, и использовать select * для тех, у кого есть уникальное имя.

например :

стол : идентификатор, имя, поле1, field2 ...

таблица b : идентификатор, имя, поле3, field4 ...

выберите а.ID в качестве помощи.имя как имя, а. * б.ID, как ставка, б.имя как имя, Б. * .....

при доступе к результатам я использую псевдонимы для этих полей и игнорирую "исходные" имена.

может быть, не самое лучшее решение, но это работает для меня....я использую mysql

этот вопрос очень полезен на практике. Необходимо только перечислить все явные столбцы в программном обеспечении, где вы уделяете особое внимание всем условиям.

представьте, что при отладке или попытке использовать СУБД в качестве ежедневного офисного инструмента вместо чего-то изменяемого реализации абстрактной базовой инфраструктуры конкретного программиста нам нужно кодировать много SQLs. Сценарий можно найти везде, как преобразование базы данных, миграция, администрация, etc. Большинство из этих SQLs будут выполняться только один раз и никогда не будут использоваться Снова, дать имена каждого столбца-это просто пустая трата времени. И не забывайте, что изобретение SQL предназначено не только для программистов.

обычно я создаю представление утилиты с префиксами имен столбцов, вот функция в pl/pgsql, это непросто, но вы можете конвертировать ее в другие языки процедур.

-- Create alias-view for specific table.

create or replace function mkaview(schema varchar, tab varchar, prefix varchar)
    returns table(orig varchar, alias varchar) as $$
declare
    qtab varchar;
    qview varchar;
    qcol varchar;
    qacol varchar;
    v record;
    sql varchar;
    len int;
begin
    qtab := '"' || schema || '"."' || tab || '"';
    qview := '"' || schema || '"."av' || prefix || tab || '"';
    sql := 'create view ' || qview || ' as select';

    for v in select * from information_schema.columns
            where table_schema = schema and table_name = tab
    loop
        qcol := '"' || v.column_name || '"';
        qacol := '"' || prefix || v.column_name || '"';

        sql := sql || ' ' || qcol || ' as ' || qacol;
        sql := sql || ', ';

        return query select qcol::varchar, qacol::varchar;
    end loop;

    len := length(sql);
    sql := left(sql, len - 2); -- trim the trailing ', '.
    sql := sql || ' from ' || qtab;

    raise info 'Execute SQL: %', sql;
    execute sql;
end
$$ language plpgsql;

примеры:

-- This will create a view "avp_person" with "p_" prefix to all column names.
select * from mkaview('public', 'person', 'p_');

select * from avp_person;

для этого нет стандарта SQL.

вместе с генерацией кода (либо по требованию, как таблицы создаются или изменяются или во время выполнения), вы можете сделать это довольно легко:

CREATE TABLE [dbo].[stackoverflow_329931_a](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [col2] [nchar](10) NULL,
    [col3] [nchar](10) NULL,
    [col4] [nchar](10) NULL,
 CONSTRAINT [PK_stackoverflow_329931_a] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[stackoverflow_329931_b](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [col2] [nchar](10) NULL,
    [col3] [nchar](10) NULL,
    [col4] [nchar](10) NULL,
 CONSTRAINT [PK_stackoverflow_329931_b] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

DECLARE @table1_name AS varchar(255)
DECLARE @table1_prefix AS varchar(255)
DECLARE @table2_name AS varchar(255)
DECLARE @table2_prefix AS varchar(255)
DECLARE @join_condition AS varchar(255)
SET @table1_name = 'stackoverflow_329931_a'
SET @table1_prefix = 'a_'
SET @table2_name = 'stackoverflow_329931_b'
SET @table2_prefix = 'b_'
SET @join_condition = 'a.[id] = b.[id]'

DECLARE @CRLF AS varchar(2)
SET @CRLF = CHAR(13) + CHAR(10)

DECLARE @a_columnlist AS varchar(MAX)
DECLARE @b_columnlist AS varchar(MAX)
DECLARE @sql AS varchar(MAX)

SELECT @a_columnlist = COALESCE(@a_columnlist + @CRLF + ',', '') + 'a.[' + COLUMN_NAME + '] AS [' + @table1_prefix + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table1_name
ORDER BY ORDINAL_POSITION

SELECT @b_columnlist = COALESCE(@b_columnlist + @CRLF + ',', '') + 'b.[' + COLUMN_NAME + '] AS [' + @table2_prefix + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2_name
ORDER BY ORDINAL_POSITION

SET @sql = 'SELECT ' + @a_columnlist + '
,' + @b_columnlist + '
FROM [' + @table1_name + '] AS a
INNER JOIN [' + @table2_name + '] AS b
ON (' + @join_condition + ')'

PRINT @sql
-- EXEC (@sql)

Я полностью понимаю вашу проблему с дублированными именами полей.

мне это тоже было нужно, пока я не закодировал свою собственную функцию, чтобы решить ее. Если вы используете PHP, вы можете использовать его или кодировать свой на языке, который вы используете, если у вас есть следующие возможности.

фокус вот в чем mysql_field_table() возвращает имя таблицы, а mysql_field_name() поле для каждой строки в результате, если он получил с mysql_num_fields() Так что вы можете смешать их в новом массиве.

это префиксы все столбцы ;)

С уважением,

function mysql_rows_with_columns($query) {
    $result = mysql_query($query);
    if (!$result) return false; // mysql_error() could be used outside
    $fields = mysql_num_fields($result);
    $rows = array();
    while ($row = mysql_fetch_row($result)) { 
        $newRow = array();
        for ($i=0; $i<$fields; $i++) {
            $table = mysql_field_table($result, $i);
            $name = mysql_field_name($result, $i);
            $newRow[$table . "." . $name] = $row[$i];
        }
        $rows[] = $newRow;
    }
    mysql_free_result($result);
    return $rows;
}

или вы можете использовать Red Gate SQL Refactor или SQL Prompt, который расширяет ваш SELECT * в списки столбцов одним нажатием кнопки Tab

Так что в вашем случае, если вы вводите SELECT * FROM a JOIN B ... Перейти к концу*, кнопка Tab, вуаля! вот увидишь Выберите A. column1, A. column2,.... , B. column1, B. column2 из соединения B

Это не бесплатно, хотя

Не могу сделать это без сглаживания , просто потому, что, как вы собираетесь ссылаться на поля в предложении where, если это поле существует в 2 или 3 стола, вы присоединяетесь? Для mysql будет неясно, на какой из них вы пытаетесь ссылаться.

Я решил аналогичную проблему, переименовав поля в соответствующих таблицах. Да, у меня была привилегия сделать это и понять, что у всех может этого не быть. Я добавил префикс к каждому полю в таблице, представляющей имя таблицы. Таким образом, SQL, опубликованный OP, останется неизменным -

SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id);

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

есть два способа, которые я могу придумать, чтобы это произошло повторно. Один из них-переименовать все ваши столбцы с префиксом для таблицы, из которой они пришли. Я видел это много раз, но мне очень не нравится. Я считаю, что это избыточно, вызывает много ввода, и вы всегда можете использовать псевдонимы, когда вам нужно покрыть случай имени столбца, имеющего неясное происхождение.

другой способ, который я рекомендую вам делать в вашей ситуации, если вы привержены это до конца, чтобы создать представления для каждой таблицы, которая псевдонимы имена таблиц. Затем вы объединяетесь с этими представлениями, а не с таблицами. Таким образом, вы можете использовать * если хотите, можете использовать исходные таблицы с оригинальными именами столбцов, если хотите, а также упростить написание любых последующих запросов, потому что вы уже выполнили работу по переименованию в представлениях.

наконец, я не понимаю, почему вам нужно знать, из какой таблицы пришел каждый из столбцов. Разве это имеет значение? В конечном счете, значение имеют данные, которые они содержат. Пришел ли UserID из таблицы User или таблицы UserQuestion, на самом деле не имеет значения. Это имеет значение, конечно, когда вам нужно обновить его, но в этот момент Вы уже должны знать свою схему достаточно хорошо, чтобы определить это.

Если вас беспокоят изменения схемы, это может сработать для вас: 1. Выполните запрос "описать таблицу" для всех задействованных таблиц. 2. Используйте Возвращенные имена полей для динамического построения строки имен столбцов с префиксом выбранного псевдонима.

существует прямой ответ на ваш вопрос для тех, кто использует MySQL C-API.

учитывая SQL:

  SELECT a.*, b.*, c.* FROM table_a a JOIN table_b b USING (x) JOIN table_c c USING (y)

результаты из 'mysql_stmt_result_metadata ()' дает определение ваших полей из вашего подготовленного SQL-запроса в структуру MYSQL_FIELD[]. Каждое поле содержит следующие данные:

  char *name;                 /* Name of column (may be the alias) */
  char *org_name;             /* Original column name, if an alias */
  char *table;                /* Table of column if column was a field */
  char *org_table;            /* Org table name, if table was an alias */
  char *db;                   /* Database for table */
  char *catalog;              /* Catalog for table */
  char *def;                  /* Default value (set by mysql_list_fields) */
  unsigned long length;       /* Width of column (create length) */
  unsigned long max_length;   /* Max width for selected set */
  unsigned int name_length;
  unsigned int org_name_length;
  unsigned int table_length;
  unsigned int org_table_length;
  unsigned int db_length;
  unsigned int catalog_length;
  unsigned int def_length;
  unsigned int flags;         /* Div flags */
  unsigned int decimals;      /* Number of decimals in field */
  unsigned int charsetnr;     /* Character set */
  enum enum_field_types type; /* Type of field. See mysql_com.h for types */

обратите внимание на поля: каталог, таблица, org_name

теперь вы знаете, какие поля в вашем SQL принадлежат к какой схеме (он же каталог) и таблица. Этого достаточно, чтобы в общем случае идентифицировать каждое поле из многостолового sql-запроса, не прибегая к псевдонимам.

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

развитие от данное решение, вот как я бы подошел к проблеме:

сначала создайте список всех AS отчетность:

DECLARE @asStatements varchar(8000)

SELECT @asStatements = ISNULL(@asStatements + ', ','') + QUOTENAME(table_name) + '.' + QUOTENAME(column_name) + ' AS ' + '[' + table_name + '.' + column_name + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE_A' OR TABLE_NAME = 'TABLE_B'
ORDER BY ORDINAL_POSITION

затем используйте его в своем запросе:

EXEC('SELECT ' + @asStatements + ' FROM TABLE_A a JOIN TABLE_B b USING (some_id)');

однако это может потребовать изменений, потому что что-то подобное тестируется только в SQL Server. Но этот код точно не работает в SQL Server, потому что использование не поддерживается.

прокомментируйте, пожалуйста, если вы можете проверить/исправить это код, например, MySQL.

недавно столкнулся с этой проблемой в NodeJS и Postgres.

ES6 подход

Я не знаю никаких функций СУБД, которые обеспечивают эту функциональность, поэтому я создал объект, содержащий все мои поля, например:

const schema = { columns: ['id','another_column','yet_another_column'] }

определен редуктор для объединения строк вместе с именем таблицы:

const prefix = (table, columns) => columns.reduce((previous, column) => {
  previous.push(table + '.' + column + ' AS ' + table + '_' + column);
  return previous;
}, []);

Это возвращает массив строк. Вызовите его для каждой таблицы и объедините результаты:

const columns_joined = [...prefix('tab1',schema.columns), ...prefix('tab2',schema.columns)];

выход последний оператор SQL:

console.log('SELECT ' + columns_joined.join(',') + ' FROM tab1, tab2 WHERE tab1.id = tab2.id');

select * обычно делает для плохого кода, так как новые столбцы, как правило, добавляются или порядок столбцов меняется в таблицах довольно часто, что обычно нарушает select * очень тонкими способами. Поэтому перечисление столбцов - это правильное решение.

Что касается того, как выполнить ваш запрос, не уверен в mysql, но в sqlserver вы можете выбрать имена столбцов из syscolumns и динамически построить предложение select.

PHP 7.2 + MySQL / Mariadb

MySQL отправит вам несколько полей с одинаковым именем. Даже в терминальном клиенте. Но если вы хотите ассоциативный массив, вам придется сделать ключи самостоятельно.

спасибо @axelbrz за оригинал. Я портировал его на более новый php и немного очистил его:

function mysqli_rows_with_columns($link, $query) {
    $result = mysqli_query($link, $query);
    if (!$result) {
        return mysqli_error($link);
    }
    $field_count = mysqli_num_fields($result);
    $fields = array();
    for ($i = 0; $i < $field_count; $i++) {
        $field = mysqli_fetch_field_direct($result, $i);
        $fields[] = $field->table . '.' . $field->name; # changed by AS
        #$fields[] = $field->orgtable . '.' . $field->orgname; # actual table/field names
    }
    $rows = array();
    while ($row = mysqli_fetch_row($result)) {
        $new_row = array();
        for ($i = 0; $i < $field_count; $i++) {
            $new_row[$fields[$i]] = $row[$i];
        }
        $rows[] = $new_row;
    }
    mysqli_free_result($result);
    return $rows;
}

$link = mysqli_connect('localhost', 'fixme', 'fixme', 'fixme');
print_r(mysqli_rows_with_columns($link, 'select foo.*, bar.* from foo, bar'));