Создайте sql запрос который помещает в таблицу users миллион записей

Как создать таблицу на 1М записей одним запросом

Допустим, вы хотите проверить, как поведет себя запрос на большой таблице — но такой таблицы под рукой нет. Если СУБД умеет в рекурсию, это не проблема: кучу данных можно нагенерить одним запросом. Поможет в этом конструкция WITH RECURSIVE .

Я буду использовать SQLite, но похожие запросы сработают в PostgreSQL и других СУБД. WITH RECURSIVE поддерживается в MariaDB 10.2+, MySQL 8.0+, PostgreSQL 8.4+ и SQLite 3.8+. Oracle 11.2+ и SQL Server 2005+ поддерживают рекурсивные запросы, но без ключевого слова RECURSIVE .

Случайные числа

Создадим таблицу на 1 млн случайных чисел:

create table random_data as with recursive tmp(x) as ( select random() union all select random() from tmp limit 1000000 ) select * from tmp; 

Или, если ваша база поддерживает generate_series() (и не поддерживает limit в рекурсивных запросах, как PostgreSQL):

create table random_data as select random() as x from generate_series(1, 1000000); 

sqlite> select count(*) from random_data; 1000000 sqlite> select avg(x) from random_data; 1.000501737529e+16 

Числовая последовательность

Вместо случайных чисел заполним таблицу числами от единицы до миллиона:

create table seq_data as with recursive tmp(x) as ( select 1 union all select x+1 from tmp limit 1000000 ) select * from tmp; 

Или через generate_series() :

create table seq_data as select value as x from generate_series(1, 1000000); 

sqlite> select count(*) from seq_data; 1000000 sqlite> select avg(x) from seq_data; 500000.5 sqlite> select min(x) from seq_data; 1 sqlite> select max(x) from seq_data; 1000000 

Рандомизированные данные

Числа — это хорошо, но что, если нужна большая табличка с данными о клиентах? Запросто!

Договоримся о правилах:

• у клиента есть идентификатор, имя и возраст;
• идентификатор заполняем последовательно от 1 до 1000000;
• имя случайным образом выбираем из фиксированного списка;
• возраст берем случайный от 1 до 80.

Создадим таблицу с именами:

create table names ( id integer primary key, name text ); insert into names(id, name) values (1, 'Анна'), (2, 'Борис'), (3, 'Вера'), (4, 'Галина'), (5, 'Денис'); 

И нагенерим клиентов:

create table person_data as with recursive tmp(id, idx, name, age) as ( select 1, 1, 'Анна', 20 union all select tmp.id + 1 as id, abs(random() % 5) + 1 as idx, (select name from names where as name, abs(random() % 80) + 1 as age from tmp limit 1000000 ) select id, name, age from tmp; 

Или через generate_series() :

create table person_data as with tmp as ( select value as id, abs(random() % 5) + 1 as idx, abs(random() % 80) + 1 as age from generate_series(1, 1000000) ) select id, (select name from names where as name, age from tmp; 

Здесь все по правилам:

• идентификатор рассчитывается как предыдущее значение + 1;
• поле idx содержит случайное число от 1 до 5;
• имя выбирается из таблицы names по значению idx ;
• возраст рассчитывается как случайное число от 1 до 80.

sqlite> select count(*) from person_data; 1000000 sqlite> select * from person_data limit 10; ┌────┬───────┬─────┐ │ id │ name │ age │ ├────┼───────┼─────┤ │ 1 │ Анна │ 20 │ │ 2 │ Анна │ 76 │ │ 3 │ Борис │ 25 │ │ 4 │ Борис │ 19 │ │ 5 │ Борис │ 11 │ │ 6 │ Вера │ 72 │ │ 7 │ Анна │ 41 │ │ 8 │ Денис │ 9 │ │ 9 │ Денис │ 38 │ │ 10 │ Вера │ 41 │ └────┴───────┴─────┘ 

Миллион клиентов одним запросом, неплохо! Вот бы в продажах так ツ

P.S. Хотите освоить современный SQL? Обратите внимание на Оконные функции

Подписывайтесь на канал, чтобы не пропустить новые заметки ��

Как быстро добавить в таблицу больше миллиона записей?

Чтобы посмотреть как будет вести себя БД в зависимости от количества записей, хочу добавить записи в таблицу для теста.

Как быстро добавить в таблицу больше миллиона записей?

94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

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

Как добавить в таблицу mdb файла Аксесса несколько записей одним запросом?
Собственно вопрос в теме. Искал по инету, но ничего путного не нашел. (хотя, может быть плохо.

Добавить не более 3-х записей в таблицу MS Access
Имеется таблица с полями Номер, Название и Группа. На ее основе создана форма с кнопкой. Как.

475 / 238 / 114
Регистрация: 12.05.2016
Сообщений: 647

936 / 693 / 236
Регистрация: 01.02.2015
Сообщений: 1,848
Регистрация: 23.12.2015
Сообщений: 730

Fedor Vlasenko,
Anvano,

Кликните здесь для просмотра всего текста

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

DROP TABLE IF EXISTS pivot; DROP TABLE IF EXISTS big_table; DROP TABLE IF EXISTS attributes; * CREATE TABLE pivot ( ROW_NUMBER INT(4) UNSIGNED AUTO_INCREMENT, PRIMARY KEY pk_pivot (ROW_NUMBER) ) engine = innodb; * INSERT INTO pivot(ROW_NUMBER) SELECT NULL FROM information_schema.global_status g1, information_schema.global_status g2 LIMIT 500; * CREATE TABLE attributes( attr_id INT(10) UNSIGNED AUTO_INCREMENT, attribute_name VARCHAR(32) NOT NULL, start_date datetime, end_date datetime, CONSTRAINT pk_attributes PRIMARY KEY(attr_id) ) engine = innodb; * CREATE TABLE big_table( btbl_id INT(10) UNSIGNED AUTO_INCREMENT, attr_attr_id INT(10) UNSIGNED, record_date datetime, record_value VARCHAR(128) NOT NULL, CONSTRAINT pk_big_table PRIMARY KEY(btbl_id) ) engine = innodb; * INSERT INTO attributes( attribute_name, start_date, end_date ) SELECT ROW_NUMBER, str_to_date("20000101", "%Y%m%d"), str_to_date("20000201", "%Y%m%d") FROM pivot; * INSERT INTO big_table( attr_attr_id, record_date, record_value) SELECT p1.ROW_NUMBER, date_add(str_to_date("20000101", "%Y%m%d"), INTERVAL p2.ROW_NUMBER + p3.ROW_NUMBER DAY), p2.ROW_NUMBER * 1000 + p3.ROW_NUMBER FROM pivot p1, pivot p2, pivot p3; * CREATE INDEX idx_big_table_attr_date ON big_table(attr_attr_id, record_date);

Сообщение по результатам добавления записей:
/* Затронуто строк: 125*001*000 Найденные строки: 0 Предупреждения: 3 Длительность 10 запросов: 01:17:08 */

Т.е. 125,0 млн записей добавлялось ~1,5 часа.

Вопрос.
1. Это нормальная скорость ?
2. Есть способы побыстрее ?

Saved searches

Use saved searches to filter your results more quickly

Cancel Create saved search

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

MySQL_IC_HomeWorks Базы данных. Интерактивный курс 2018/09/18

Alex-Smil/MySQL_IC_HomeWorks

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Switch branches/tags
Branches Tags
Could not load branches
Nothing to show
Could not load tags
Nothing to show

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Cancel Create

• Local
• Codespaces

HTTPS GitHub CLI
Use Git or checkout with SVN using the web URL.
Work fast with our official CLI. Learn more about the CLI.

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

Latest commit message
Commit time

README.md

MySQL_IC_HomeWorks

MySQL_IC_HomeWorks Базы данных. Интерактивный курс 2018/09/18

Урок 2 — Язык запросов SQL

1. Пусть в таблице catalogs базы данных shop в строке name могут находиться пустые строки и поля, принимающие значение NULL. Напишите запрос, который заменяет все такие поля на строку ‘empty’. Помните, что на уроке мы установили уникальность на поле name. Возможно ли оставить это условие? Почему?
2. Спроектируйте базу данных, которая позволяла бы организовать хранение медиафайлов, загружаемых пользователем (фото, аудио, видео). Сами файлы будут храниться в файловой системе, а база данных будет содержать только пути к файлам, названия, описания, ключевых слов и принадлежности пользователю.
3. (по желанию) В учебной базе данных shop присутствует таблица catalogs. Пусть в базе данных sample имеется таблица cat, в которой могут присутствовать строки с такими же первичными ключами. Напишите запрос, который копирует данные из таблицы catalogs в таблицу cat, при этом для записей с конфликтующими первичными ключами в таблице cat должна производиться замена значениями из таблицы catalogs.

Урок 3 — Операторы, фильтрация, сортировка и ограничение

1. Пусть в таблице users поля created_at и updated_at оказались незаполненными. Заполните их текущими датой и временем. 2.Таблица users была неудачно спроектирована. Записи created_at и updated_at были заданы типом VARCHAR и в них долгое время помещались значения в формате «20.10.2017 8:10». Необходимо преобразовать поля к типу DATETIME, сохранив введеные ранее значения.
2. В таблице складских запасов storehouses_products в поле value могут встречаться самые разные цифры: 0, если товар закончился и выше нуля, если на складе имеются запасы. Необходимо отсортировать записи таким образом, чтобы они выводились в порядке увеличения значения value. Нулевые запасы должны выводиться в конце, после всех записей.
3. (по желанию) Из таблицы users необходимо извлечь пользователей, родившихся в августе и мае. Месяцы заданы в виде списка английских названий (‘may’, ‘august’)
4. (по желанию) Из таблицы catalogs извлекаются записи при помощи запроса. SELECT * FROM catalogs WHERE id IN (5, 1, 2); Отсортируйте записи в порядке, заданном в списке IN.

Урок 4 — Агрегация данных

1. Подсчитайте средний возраст пользователей в таблице users.
2. Подсчитайте количество дней рождения, которые приходятся на каждый из дней недели. Следует учесть, что необходимы дни недели текущего года, а не года рождения.
3. (по желанию) Подсчитайте произведение чисел в столбце таблицы.

Урок 5 — Сложные запросы

1. Составьте список пользователей users, которые осуществили хотя бы один заказ (orders) в интернет-магазине.
2. Выведите список товаров products и разделов catalogs, который соответствует товару.
3. (по желанию) Есть таблица рейсов flights (id, from, to) и таблица городов cities (label, name). Поля from, to и label содержат английские названия городов, поле name — русское. Выведите список рейсов (flights) с русскими названиями городов.

Урок 6 — Транзакции, переменные, представления

1. В базе данных shop и sample присутвуют одни и те же таблицы учебной базы данных. Переместите запись из таблицы shop.users в таблицу sample.users. Используйте транзакции.
2. Создайте представление, которое выводит название (name) товарной позиции из таблицы products и соответствующее название (name) каталога из таблицы catalogs.
3. (по желанию) Пусть имеется таблица с календарным полем created_at. В ней размещены разряженые календарные записи за август 2018 года ‘2018-08-01’, ‘2016-08-04’, ‘2018-08-16’ и 2018-08-17. Составьте запрос, который выводит полный список дат за август, выставляя в соседнем поле значение 1, если дата присутствует в исходном таблице и 0, если она отсутствует.
4. (по желанию) Пусть имеется любая таблица с календарным полем created_at. Создайте запрос, который удаляет устаревшие записи из таблицы, оставляя только 5 самых свежих записей.

Урок 7 — Администрирование MySQL

1. Создайте двух пользователей которые имеют доступ к базе данных shop. Первому пользователю shop_read должны быть доступны только запросы на чтение данных, второму пользователю shop — любые операции в пределах базы данных shop.
2. (по желанию) Есть таблица (accounts), включающая в себя три столбца: id, name, password, которые содержат первичный ключ, имя пользователя и его пароль. Создайте представление username таблицы accounts, предоставляющее доступ к столбцам id и name. Создайте пользователя user_read, который бы не имел доступа к таблице accounts, однако мог извлекать записи из представления username.

Урок 10 — Хранимые процедуры и функции, триггеры

1. Создайте хранимую функцию hello(), которая будет возвращать приветствие, в зависимости от текущего времени суток. С 6:00 до 12:00 функция должна возвращать фразу «Доброе утро», с 12:00 до 18:00 функция должна возвращать фразу «Добрый день», с 18:00 до 00:00 — «Добрый вечер», с 00:00 до 6:00 — «Доброй ночи».
2. В таблице products есть два текстовых поля: name с названием товара и description с его описанием. Допустимо присутствие обоих полей или одно из них. Ситуация, когда оба поля принимают неопределенное значение NULL неприемлема. Используя триггеры, добейтесь того, чтобы одно из этих полей или оба поля были заполнены. При попытке присвоить полям NULL-значение необходимо отменить операцию.
3. (по желанию) Напишите хранимую функцию для вычисления произвольного числа Фибоначчи. Числами Фибоначчи называется последовательность в которой число равно сумме двух предыдущих чисел. Вызов функции FIBONACCI(10) должен возвращать число 55.

Урок 9 — Оптимизация запросов

1. Создайте таблицу logs типа Archive. Пусть при каждом создании записи в таблицах users, catalogs и products в таблицу logs помещается время и дата создания записи, название таблицы, идентификатор первичного ключа и содержимое поля name.
2. (по желанию) Создайте SQL-запрос, который помещает в таблицу users миллион записей.

1. В базе данных Redis подберите коллекцию для подсчета посещений с определенных IP-адресов.
2. При помощи базы данных Redis решите задачу поиска имени пользователя по электронному адресу и наоброт, поиск электронного адреса пользователя по его имени.
3. Организуйте хранение категорий и товарных позиций учебной базы данных shop в СУБД MongoDB.

About

MySQL_IC_HomeWorks Базы данных. Интерактивный курс 2018/09/18

Работа с MySQL в PHP

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

Сценарий, который подключается к БД, выполняет запрос и показывает результат, будет состоять всего из нескольких строк. Для работы с MySQL не надо ничего дополнительно устанавливать и настраивать; всё необходимое уже доступно вместе со стандартной поставкой PHP.

Что такое mysqli?

mysqli (MySQL Improved) — это расширение PHP, которое добавляет в язык полную поддержку баз данных MySQL. Это расширение поддерживает множество возможностей современных версий MySQL.

Как выглядит работа с базой данных

Типичный процесс работы с СУБД в PHP-сценарии состоит из нескольких шагов:

1. Установить подключение к серверу СУБД, передав необходимые параметры: адрес, логин, пароль.
2. Убедиться, что подключение прошло успешно: сервер СУБД доступен, логин и пароль верные и так далее.
3. Сформировать правильный SQL запрос (например, на чтение данных из таблицы).
4. Убедиться, что запрос был выполнен успешно.
5. Получить результат от СУБД в виде массива из записей.
6. Использовать полученные записи в своём сценарии (например, показать их в виде таблицы).

Функция mysqli connect: соединение с MySQL

Перед началом работы с данными внутри MySQL, нужно открыть соединение с сервером СУБД. В PHP это делается с помощью стандартной функции mysqli_connect() . Функция возвращает результат — ресурс соединения. Данный ресурс используется для всех следующих операций с MySQL.

Но чтобы выполнить соединение с сервером, необходимо знать как минимум три параметра:

• Адрес сервера СУБД;
• Логин;
• Пароль.

Если вы следовали стандартной процедуре установки MySQL или используете OpenServer, то адресом сервера будет localhost , логином — root . При использовании OpenServer пароль для подключения — это пустая строка ‘’ , а при самостоятельной установке MySQL пароль вы задавали в одном из шагов мастера установки.

Базовый синтаксис функции mysqli_connect() :

mysqli_connect(, , , ); 

Проверка соединения

Первое, что нужно сделать после соединения с СУБД — это выполнить проверку, что оно было успешным. Эта проверка нужна, чтобы исключить ошибку при подключении к БД. Неверные параметры подключения, неправильная настройка или высокая нагрузка заставит MySQL отвергать новые подключения. Все эти ситуации приведут к невозможности соединения, поэтому программист должен проверить успешность подключения к серверу, прежде чем выполнять следующие действия.

Соединение с MySQL устанавливается один раз в сценарии, а затем используется при всех запросах к БД.

Результатом выполнения функции mysqli_connect() будет значение специального типа — ресурс. Если подключение к MySQL не удалось, то функция mysqli_connect() вместо ресурса вернёт логическое значение типа «ложь» — false . Хорошей практикой будет всегда проверять результат выполнения этой функции и сравнивать его с ложью.

Соединение с MySQL и проверка на ошибки:

 else < print("Соединение установлено успешно"); >?> 

Функция mysqli_connect_error() просто возвращает текстовое описание последней ошибки MySQL.

Установка кодировки

Первым делом после установки соединения крайне желательно явно задать кодировку, которая будет использоваться при обмене данными с MySQL. Если этого не сделать, то вместо записей со значениями, написанными кириллицей, можно получить последовательность из знаков вопроса: . . Вызовите эту функцию сразу после успешной установки соединения: mysqli_set_charset($con, «utf8»);

Выполнение запросов

Установив соединение и определив кодировку мы готовы выполнить свои первые SQL-запросы. Вы уже умеете составлять корректные SQL команды и выполнять их через консольный или визуальный интерфейс MySQL-клиента. Те же самые запросы можно отправлять без изменений и из PHP-сценария. Помогут в этом несколько встроенных функций языка.

Два вида запросов

Следует разделять все SQL-запросы на две группы:

1. Чтение информации (SELECT).
2. Модификация (UPDATE, INSERT, DELETE).

При выполнении запросов из среды PHP, запросы из второй группы возвращают только результат их исполнения: успех или ошибку.

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

Добавление записи

Вернёмся к нашему проекту — дневнику наблюдений за погодой. Начнём практическую работу с заполнения таблиц данными. Для начала добавим хотя бы один город в таблицу cities .

Выражение INSERT INTO используется для добавления новых записей в таблицу базы данных.

Составим корректный SQL-запрос на вставку записи с именем города, а затем выполним его путём передачи этого запроса в функцию mysqli_query() , чтобы добавить новые данные в таблицу.

Обратите внимание, что первым параметром для функции mysqli_query() передаётся ресурс подключения, полученный от функции mysqli_connect() , вторым параметром следует строка с SQL-запросом.

При запросах на изменение данных (не SELECT) результатом выполнения будет логическое значение — true или false , которое будет означать, что запрос выполнить не удалось. Для получения строки с описанием ошибки существует функция mysqli_error($link) .

Функция insert id: как получить идентификатор добавленной записи

Следующим шагом будет добавление погодной записи для нового города. Погодные записи хранит таблица weather_log, но, чтобы сослаться на город, необходимо знать идентификатор записи из таблицы cities.

Здесь пригодится функция mysqli_insert_id() . Она принимает единственный аргумент — ресурс соединения, а возвращает идентификатор последней добавленной записи.

Теперь у нас есть всё необходимое, чтобы добавить погодную запись. Вот как будет выглядеть комплексный пример с подключением к MySQL и добавлением двух новых записей:

 else < $sql = 'INSERT INTO cities SET name = "Санкт-Петербург"'; $result = mysqli_query($link, $sql); if ($result == false) < print("Произошла ошибка при выполнении запроса"); >else < $city_id = mysqli_insert_id($link); $sql = 'INSERT INTO weather_log SET city_id = ' . $city_id . ', day = "2017-09-03", temperature = 10, cloud = 1'; $result = mysqli_query($link, $sql); if ($result == false) < print("Произошла ошибка при выполнении запроса"); >> > 

Чтение записей

Другая частая операция при работе с базами данных в PHP — это получение записей из таблиц (запросы типа SELECT). Составим SQL-запрос, который будет использовать SELECT выражение. Затем выполним этот запрос с помощью функции mysqli_query() , чтобы получить данные из таблицы.

В этом примере показано, как вывести все существующие города из таблицы cities :

В примере выше результат выполнения функции mysqli_query() сохранён в переменной $result . В этой переменной находятся не данные из таблицы, а специальный тип данных — так называемая ссылка на результаты запроса.

Чтобы получить действительные данные, то есть записи из таблицы, следует использовать другую функцию — mysqli_fetch_array() — и передать ей единственным параметром эту самую ссылку. Теперь каждый вызов функции mysqli_fetch_array() будет возвращать следующую запись из всего результирующего набора записей в виде ассоциативного массива.

Цикл while здесь используется для «прохода» по всем записям из полученного набора записей. Значение поля каждой записи можно узнать просто обратившись по ключу этого ассоциативного массива.

Как получить сразу все записи в виде двумерного массива

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

Функция mysqli_fetch_all($res, MYSQLI_ASSOC) вернёт двумерный массив со всеми записями из результата последнего запроса. Перепишем пример с показом существующих городов с её использованием:

Как узнать количество записей

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

«Доктайп» — журнал о фронтенде. Читайте, слушайте и учитесь с нами.