Добавить в одно поле несколько значений из другой таблицы, sql
Постараюсь описать ситуацию У меня есть таблица с пользователями и в ней поле факторов и таблица с несколькими факторами. Можно ли их как-то связать так, чтобы можно было присвоить одному пользователю несколько факторов, сохранив это всё в одно поле? Чтобы под каждый фактор не создавать отдельное поле. Я конечно не уверена, но это можно сделать связью М:М и доп. таблицей?
Отслеживать
задан 15 сен 2020 в 11:34
69 10 10 бронзовых знаков
Да, это так и делается. Это не «можно сделать связью М:М», это является связью М:М. В доп таблице должны быть обязательно два столбца — id пользователя и id фактора
15 сен 2020 в 11:37
15 сен 2020 в 11:40
Хранение нескольких значений в одном поле (CSV) — плохая практика. Такие данные очень плохо обрабатываются, ибо не индексируются — т.е. каждый запрос будет порождать фуллскан таблицы. это можно сделать связью М:М и доп. таблицей? Нужно.
15 сен 2020 в 11:41
Ага, поняла. Может есть какой либо другой вариант решения?
15 сен 2020 в 11:44
Зачем искать другой способ, когда уже есть один — хороший, высокопроизводительный, проверенный временем и кучей народу?
15 сен 2020 в 11:55
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Сохранение нескольких значений в одно поле является нарушением первой нормальной формы реляционной модели. Реляционные СУБД, чтобы называться реляционными должны не позволять ее нарушать.
Так что прямой ответ — нет, нельзя.
И вообще как только в постановке задачи возникает слово «несколько», значит речь идет о таблице.
В вашем случае — о дополнительной таблице связей М:М
PS Существуют способы обхода ограничения, например, преобразовав некоторое количество свойств в одну длинную строку (CSV, XML, JSON. ), но они замедляют как разработку так и работу БД
Как добавить несколько строк в таблицу sql
Если я правильно понял, то вопрос в том, чтобы добавить в таблицу сразу несколько строк одним запросом. Используем INSERT INTO :
INSERT INTO cups (name, price) VALUES ('Cup With Rabbit', 29.99), ('My Favorite Cup', 41.99), ('Cuppy Cup', 52.99); -- Мы добавили сразу три строки в таблицу с именем cups Оператор INSERT
Оператор INSERT вставляет новые записи в таблицу. При этом значения столбцов могут представлять собой литеральные константы, либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT ; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.
Синтаксис оператора следующий:
Как видно из представленного синтаксиса, список столбцов не является обязательным (об этом говорят квадратные скобки в описании синтаксиса). В том случае, если он отсутствует, список вставляемых значений должен быть полный, то есть обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, эти значения должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. В качестве примера рассмотрим вставку строки в таблицу Product, созданную следующим оператором CREATE TABLE :
Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором:
Если задать список столбцов, то можно изменить «естественный» порядок их следования:
Казалось бы, это совершенно излишняя возможность, которая делает конструкцию только более громоздкой. Однако она становится выигрышной, если столбцы имеют значения по умолчанию. Рассмотрим следующую структуру таблицы:
Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два — NULL, а последний столбец — type — PC). Теперь мы могли бы написать:
В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию — PC. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL , запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL .
Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT :
Поскольку все столбцы имеют значения по умолчанию, для вставки строки со значениями по умолчанию можно было бы написать:
Однако для этого случая предназначена специальная конструкция DEFAULT VALUES (см. синтаксис оператора), с помощью которой вышеприведенный оператор можно переписать в виде
Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK , ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отклонена. Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Product_D все строки из таблицы Product, относящиеся к моделям персональных компьютеров (type = ‘PC’). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:
Использование в подзапросе символа «*» является в данном случае оправданным, так как порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы применить список столбцов либо в операторе INSERT , либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов:
SQL INSERT INTO: примеры вставки строк в таблицу БД MySQL
SQL оператор INSERT используется для вставки записей в существующую таблицу.
Синтаксис этого оператора следующий:
INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name. )] VALUES (expression. ),(. ). или INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name. )] SELECT . или INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name=expression, col_name=expression, .
Создадим тестовую таблицу
Давайте создадим таблицу table1 со столбцами a, b, c в нашей MySQL базе данных:
CREATE TABLE `table1` ( `a` INT(11), `b` INT(11), `c` INT(11) );
Запрос на вставку строки
Простой запрос, который вставляет строку со столбцами 111, 222 и 333 выглядит так:
INSERT INTO table1 (a, b, c) VALUES (111, 222, 333);
Еще один способ сделать то же самое:
INSERT INTO table1 SET a=111, b=222, c=333;
Столбцы, которые вы не перечислите заполняются значениями по умолчанию, которые вы предусматриваете при создании таблицы, даже если это просто NULL.
У таблиц обычно есть поле id с первичным ключом (PRIMARY KEY) таблицы. Если этому полю установлено значение AUTOINCREMENT т.е. оно заполняется автоматически, то в таком случае вы не должны его перечислять в списке столбцов оператора INSERT.
Вставка без перечисления столбцов
Если количество значений, которые мы вставляем = количеству столбцов в таблице, то можно не перечислять столбцы, и наш запрос может выглядеть так:
INSERT INTO table1 VALUES (111, 222, 333);
Этот способ крайне не рекомендуется. Дело в том, что со временем вы можете менять таблицы, например добавлять в них новые столбцы, а это значит, что все запросы записанные таким способом просто перестанут работать и вам придется менять их по всему вашему приложению. Поэтому, навсегда забываем этот способ. Я его привел, только чтобы вы так не делали.
Вставка сразу нескольких строк с помощью INSERT INTO
Если нам нужно вставить несколько строк, то мы просто перечисляем группы значений через запятую выглядит это так:
INSERT INTO table1 (a, b, c) VALUES (1, 222, 333), (2, 555, 666), (3, 555, 333);
Таким образом мы вставили 3 строки в нашу таблицу table1. Их может быть и больше. В MySQL четкого предела нет, однако он все таки существует и зависит от параметра max_allowed_packet который ограничивает размер запроса. Если вы установите SET GLOBAL max_allowed_packet=524288000; то размер запроса будет ограничен 500MB но делайте это в очень крайнем случае. Обычно всегда можно найти решение и разделить 1 большой запрос, на несколько более мелких и вставлять например не больше 1000 строк за один цикл.
Как вставить значение из другой таблицы INSERT INTO . SELECT .
Допустим у нас есть еще одна таблица table2 которая по структуре точно такая же как и первая. Нам в таблицу table2 нужно вставить все строки из table1.
Вставляем значения из table1 в таблицу table2:
INSERT INTO table2 (a, b, c) SELECT a, b, c FROM table1;
Вам следует позаботиться об уникальности ключей, если они есть в таблице, в которую мы вставляем. Например при дублировании PRIMARY KEY мы получим следующее сообщение об ошибке:
/* ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY' */
Если вы делаете не какую-то единичную вставку при переносе данных, а где-то сохраните этот запрос, например в вашем PHP скрипте, то всегда перечисляйте столбцы.
Как не рекомендуется делать (без перечисления столбцов):
INSERT INTO table2 SELECT * FROM table1;
Если у вас со временем изменится количество столбцов в таблице, то запрос перестанет работать. При выполнении запроса MySQL в лучшем случае просто будет возвращать ошибку:
/* Ошибка SQL (1136): Column count doesn't match value count at row 1 */
Либо еще хуже: значения вставятся не в те столбцы.
Вставка из другой таблицы с условием INSERT INTO . SELECT . WHERE .
А теперь представим, что нам нужно вставить только те строки из table1, у которых столбец «c» равен 333. Тогда наш запрос будет выглядеть так
INSERT INTO table2 (a, b, c) SELECT a, b, c FROM table1 WHERE c = 333;
То есть мы просто вставляем данные в таблицу, которые выбрали из другой таблицы при помощи обычного SELECT запроса
Теперь представим, что у нас в таблице table2 — 4 столбца, а в table1 — 3. При этом четвертый столбец в table2 обязательный. Чтобы выйти из этой ситуации, нужно передать какое-нибудь подходящее значение в этот лишний столбец. У нас чисто абстрактная задача, поэтому давайте передадим туда просто единицу.
INSERT INTO table2 (a, b, c, d) SELECT a, b, c, 1 FROM table1 WHERE c = 333;
Теперь в столбец d у нас записалась единица и проблема решена.
Вставка в определенный раздел INSERT INTO . PARTITION .
Если вам нужно вставить строки в определенный раздел таблицы, то нужно после таблицы указать PARTITION (название раздела), например так:
INSERT INTO table1 PARTITION (p1) VALUES(1, 2, 3);
Вставка в несколько разделов. Первая строка вставляется в раздел p1, а вторая в p2
INSERT INTO table1 PARTITION (p1, p2) VALUES(1, 2, 3), (4, 5, 6);
Вставка строк, некоторые из которых уже существуют в целевой таблице
Существование строк определяется по значению уникальных ключей. В зависимости от ситуации мы можем выбрать разные способы поведения при совпадении значений уникальных столбцов.
Игнорирование INSERT IGNORE INTO
Например если мы вставляем строку с PK = 1, и при этом в таблице уже есть PK = 1 то MySQL выдаст ошибку:
/* ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' */
Выполнение запроса на этом прервется, однако нам в некоторых случаях хотелось бы просто вставить данные, игнорируя ошибки. В этом нам поможет INSERT IGNORE INTO:
INSERT IGNORE INTO table1 (a, b, c) VALUES (1, 222, 333), (2, 555, 666), (3, 555, 333);
Просто добавляем IGNORE в наш запрос и ошибки будут игнорироваться
Вставка с заменой существующих значений REPLACE INTO
REPLACE работает также INSERT, но если совпадают уникальные ключи, то старая строка (или строки!) удаляется до вставки новой.
В таком случае наш пример выглядит следующим образом:
REPLACE INTO table1 (a, b, c) VALUES (1, 222, 333), (2, 555, 666), (3, 555, 333);
Обновление некоторых полей, при существовании строк ON DUPLICATE KEY UPDATE
При совпадении ключей, мы можем также заменить некоторые или все поля в строке.
Наш запрос будет выглядеть так:
INSERT INTO table1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=VALUES(c);
В данном примере если у нас какой-то уникальный ключ совпадает, то мы не производим вставку, а обновляем существующую строку или строки путем присваивания столбцу «c» значения, которое у нас перечислено в VALUES.
Иными словами, если ключ совпадает, то мы просто обновим данные столбца «с» а остальные столбцы трогать не будем.
Иногда нам нужно при совпадении ключей обновить все значения. Этом можно сделать просто перечислив все столбцы:
INSERT INTO table1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c);
При обновлении столбцов мы также можем использовать разные выражения, например:
INSERT INTO table1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
Выражения для вставляемых значений в VALUES
При вставке значений, мы можем использовать выражения и даже использовать в своих выражениях значения других столбцов.
Пример использования выражений:
INSERT INTO table1 (a, b, c) VALUES (1, 222, 333 + a), (2, 555, 666 + b);
Таким образом мы для формирования столбца «c» использовали столбцы «a» и «b».
Приоритет вставки INSERT LOW_PRIORITY / HIGH_PRIORITY
Установление приоритета нужно для решение проблем с конкурентными вставками. При вставках происходит блокировка строк и если 2 INSERT запроса требуют блокировки одних и тех же строк, для своего выполнения, то иногда может потребоваться повысить или понизить приоритет некоторых запросов, по отношению к другим. Это можно сделать указав приоритет LOW_PRIORITY или HIGH_PRIORITY
Наш запрос будет выглядеть так для LOW_PRIORITY:
INSERT LOW_PRIORITY INTO table1 (a, b, c) VALUES(1, 2, 3);
INSERT HIGH_PRIORITY INTO table1 (a, b, c) VALUES(1, 2, 3);
Свежие записи
- SQL UPDATE: примеры обновления строк в таблице
- PHP: substr и мощные альтернативы, чтобы вырезать часть строки
- Степень в C, C++ и С#: как возвести число в любую степень, побитовые операции
- Скачать ShowKeyPlus: ссылка на последнюю официальную версию, скачивание, установка
- Как создать файл в Linux: 12 способов
- SQL INSERT INTO: примеры вставки строк в таблицу БД MySQL
- PHP: str_replace — замена или удаление подстроки в строке
- Функция date() в php: распространенные форматы, примеры, советы
- cURL в PHP: примеры POST, GET запросов с headers, cookie, JSON и многопоточностью
- JSON в PHP: примеры json_encode, json_decode, работа с кириллицей и utf-8
- Файл gitignore — примеры и документация
- Сортировка массивов в php: ksort, asort и прочие sort’ы
- jQuery onclick: как выполнить код при клике на кнопку
- 500 Internal Server Error — в чем причина?
- SMTP от Yandex: как отправить письма из PHP — пример настроек
- No such file or directory — в чем причина?
- Как получить первый элемент массива в php
- Cтроку в массив по разделителю в PHP: explode, str_split, strtok — что выбрать?
- 301 редирект на https с www через htaccess
- Как в php добавить к массиву другой массив?
- 301 редирект на https без www с помощью htaccess
- Регулярные выражения в PHP
- PHP json_decode — как декодировать результат в массив?
- Yii2 ActiveRecord шпаргалка по составлению запросов
- Поиск подстроки в строке с помощью PHP
- Отправка почты на php
- Как подключить php код в html
- Конвертировать массив в строку при помощи PHP
- Подключение одного php файла в другой
- Подборка ссылок для веб-разработчика
- Проблема с кириллицей в PHPWord