Запросы в MySQL

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

Как я могу легко создать 100 000 строк случайных/мусорных данных для моей таблицы базы данных?

Каков ваш способ создания и заполнения тестовых таблиц в MySQL при рассмотрении вопросов SO, в которых предоставляются таблицы (но не CREATE) и тестовые данные (но не INSERT)?

А каким клиентом пользуетесь?

РЕДАКТИРОВАТЬ: Вот простой пример того, что я имею в виду на SO .

Вводное слово

Система тестирования разрабатывалась в рамках и контексте рабочего проекта, в котором используется своя ORM-система работы с базой данных, которая принимает параметризованные SQL-запросы и на выходе самостоятельно разбирает полученные табличные данные в объекты. Что накладывает некоторые ограничения при разработке системы тестирования.

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

Описание проблемы

Перед тем как начать рассказ о разработке, ещё хотелось бы рассказать, как в большинстве случаев тестируют такие вещи и к чему это может привести.

Тестирование запроса в базе данных

Перед тем как разработчик написал метод, который взаимодействует с базой данных, он пишет обычный SQL-запрос с параметрами и проверяет его работу на своей локальной копии базы. Это самый быстрый и простой способ проверить запрос на ошибки. Но стоит обратить внимание, что с помощью такого подхода можно допустить ошибку во время переноса запроса в код проекта или в коде, выполняющем этот запрос. Как пример: можно по случайности пропустить инициализацию некоторых параметров, что приведёт к некорректной работе запроса.

Тестирование через прямой вызов метода

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

Тестирование через пользовательский интерфейс приложения

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

И все эти сложности приводят к тому, что приходится тратить много времени на тестирование достаточно простых вещей. А как известно, если это проверять долго, то скорее всего разработчик полениться тестировать, что приведёт к ошибкам. По статистике около 5% ошибок, связанных с неработающими запросами к базе данных, регистрируются в баг-трекере. Но также я хочу отметить, что большинство таких ошибок обсуждается устно и достаточно часто, и к сожалению, их невозможно учесть в этой статистике.

Основные запросы.

select r.number as маршрут, sum(t.transportetPassengers) as перевезено, to_char(sum(t.transportetPassengers * t.ticketPrice), ‘9999999D99’) || ‘ руб’ as выручка from Trips t join t.routes r where t.timeStop between :start and :stop group by r.number

select d.fullName as имя, d.passportSn as сн_паспорта, to_char((d.baseSalary * (extract(year from age(:stop, :start)) * 12

+ extract(month from age(:stop, :start))) + sum(t.transportetPassengers * t.ticketPrice * d.percentFromTickets / 100))*0.87, ‘9999999D99’) || ‘ руб’ as зар_плата from Trips t join t.drivers d where t.timeStop between :start and :stop group by d.passportSn, d.fullName

select c.registrationNumber as рег_номер, to_char(sum(s.fuelPrice*s.fuelQuantity + s.oilPrice*s.oilQuantity + s.serviceExpenses), ‘9999999D99’) || ‘ руб’ as затраты from Service s join s.cars c where s.time between :start and :stop group by c.registrationNumber

//java-сервис, обеспечивающий подсчёт прибыли

public class ProfitGetter extends com.wavemaker.runtime.javaservice.JavaServiceSuperClass <

public void openReport()<

log(INFO, «Trying to open report»);

log(INFO, «Report has been successfully opened!»);

log(ERROR, «Java service operation has failed (openReport)», e);

public String getProfit(java.sql.Date start, java.sql.Date stop) <

String result = null;

String database = «postgres»;

String username = «postgres»;

String password = «14159265»;

log(INFO, «Getting connection»);

Class.forName(«org.postgresql.Driver»); //load the driver

Connection connection = DriverManager.getConnection(«jdbc:postgresql:»+database,

password); //connect to the db

log(INFO, «Connection recieved!»);

log(INFO, «Getting base salary from db»);

Statement statement = connection.createStatement(

ResultSet resultSet = statement

.executeQuery(«select sum(d.base_Salary * (extract(year from age(date ‘» +stop.toString() + «‘, date ‘» + start.toString() + «‘)) * 12 + extract(month from age(date ‘» +stop.toString() + «‘, date ‘» + start.toString() + «‘)))) as salary from Drivers as d»);

log(INFO, «. Parsing RS. «);

BigDecimal salary = null;

log(INFO, «Base salary successfully recieved!»);

log(INFO, «Getting balance from db»);

.executeQuery(«select sum(f.money) as balance from For_Profit as f where f.time between date ‘» + start.toString() + «‘ and date ‘» +stop.toString() + «‘»);

log(INFO, «. Parsing RS. «);

BigDecimal balance = null;

log(INFO, «Balance successfully recieved!»);

log(INFO, «Returning » + result);

log(ERROR, «Java service operation has failed (getProfit)», e);

Для входа в программу, достаточно запустить программу.

Главное меню программы

img Ltz 1M

На главной панели программы изображено 7 (плюс вызов помощи – красный знак вопроса) вкладок, позволяющих производить различные операции и запросы. Все эти операции рассмотрены ниже поочередно.

Так же на главной странице размещен титульный лист работы.

Для добавления данных в ту или иную таблицу следует нажать на кнопку «Добавить» в правом нижнем углу, заполнить все необходимые поля и нажать кнопку «Далее». Рассмотрим пример:

img vs UE6

img zFcUt8

img Hd1FYQ

Для изменения данных в той или иной таблице требуется выбрать интересующую строку и нажать кнопку «Обновить». Далее, изменив те или иные поля, необходимо нажать кнопку «Далее». Продемонстрируем на примере:

img MVDNF2

img LTDTVU

img CINqpa

img zdJYCa

Для удаления данных из таблицы необходимо выделить интересующую строку и нажать кнопку «Удалить». После чего следует нажать кнопку «Ок» в всплывающем сообщении. Всплывающее сообщение выводится на английском языке на случай непонимания пользователем языка интерфейса программы. Рассмотрим на примере:

img jZ8ERt

img

img hQS7J3

Запросы находятся во вкладке Запросы. Для выполнения запроса необходимо заполнить поля, отмеченные красной звёздочкой (*) и нажать кнопку «Обновить». Продемонстрируем на примере:

img M8kssV

img lw3jQP

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

Источник

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

У меня есть широкая таблица с примерно 40 полями разных видов(int, bit, varchar и т. д.) и нужно провести тестирование производительности. Я использую SQL Server 2008.

4 ответов


генератор данных SQL по Редгейт

генерация данных в один клик

реалистичные данные, основанные на имени столбца и таблицы

данные можно подгонять если пожелано

устраняет часы утомительной работы

полная поддержка SQL Server 2008



имейте в виду, что импорт a .csv-файл с использованием SSMS или пользовательских сценариев импорта SQL проще, чем создание SQL вставляет вручную, но есть некоторые ограничения, как описано в учебнике:

Если есть необходимость в тысячах строк для заполнения и.csv-файл содержит несколько сотен строк данных, которых просто недостаточно. Способ импортировать то же самое .csv-файл снова и снова, пока не понадобится. Недостатком этого метода является то, что он вставляет большие блоки строк с одинаковыми данными без их рандомизации.


Как упоминалось в «SQLMenace», Redgate Data Generator-настолько хороший инструмент для его выполнения, что он стоит $ 369, у вас есть 14-дневный пробный шанс.

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

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

Я заполнил свои таблицы более чем 10 000 000 записей, что было потрясающей симуляцией.


обновление:
Вам нужно только Go 1000 после вставки, чтобы заполнить его 1000 раз, так же, как это:

INSERT INTO dbo.Cusomers(Id, FirstName, LastName) VALUES(1, 'Mohamed', 'Mousavi')
GO 1000

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

INSERT INTO dbo.Customers
SELECT * FROM dbo.Customers 
GO 10

в случае, если один или несколько столбцов идентичность означает, что они принимают уникальные значения, если это автоматический инкрементный, вы просто не помещаете его в запрос, например, если Id в dbo.Клиент-это личность, запрос идет следующим образом:

INSERT INTO dbo.Customers
SELECT FirstName, Last Name FROM dbo.Customers
GO 10
INSERT INTO dbo.Customers
SELECT Id, FirstName, Last Name FROM dbo.Customers
GO 10

иначе вы столкнетесь с такой ошибкой:

An explicit value for the identity column in table ‘dbo.Customers’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

Примечание.:
Это своего рода арифметическая прогрессия, поэтому она будет длиться немного, не используйте большое число перед GO.

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

  1. выберите одну из ваших таблиц, которая имеет замечательное количество строк, скажем dbo.Клиенты

  2. щелкните правой кнопкой мыши на нем и выберите Script Table as > Create To > New Query Editor Window

  3. назовите свою новую таблицу чем-то еще вроде dbo.CustomersTest, теперь вы можете выполнить запрос, чтобы иметь новую таблицу с аналогичной структурой с dbo.Клиенты.

Примечание: имейте в виду, что если у него есть идентификатор, измените его Identity Specification to No так как вы должны заполнить новую таблицу данными исходной неоднократно.

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

  2. через некоторое время у вас есть таблица с фиктивными строками в ней!


Я просто хотел знать, как сделать цикл и заполнить таблицу базы данных поддельными данными, чтобы получить 500 000 записей. У меня есть таблица со следующими полями, для customer_id у нас 1-1000, staff_id у нас 1-5 сотрудников, car_id составляет от 1 до 10,000, qty составляет 1-3, date_ordered — с 1975 по 2017, date_returned — с 1975 по 2017 г. Для дат разница между date_ordered и date_returned должна составлять 2-3 дня.

Любая помощь по этому вопросу будет высоко ценится!

CREATE TABLE car_transaction
(
  transaction_id INTEGER NOT NULL,
  customer_id INTEGER,
  staff_id INTEGER,
  car_ID INTEGER,
  QTY INTEGER,
  date_ordered,
  date_returned,
  PRIMARY KEY (transaction_id));

Это вполне возможно только с чистым кодом MySQL SQL.

Это таблица, которую я использовал

CREATE TABLE car_transaction
(
  transaction_id INTEGER NOT NULL AUTO_INCREMENT, # included AUTO_INCREMENT HERE
  customer_id INTEGER,
  staff_id INTEGER,
  car_ID INTEGER,
  QTY INTEGER,
  date_ordered DATE, # made DATE type
  date_returned DATE, # made DATE type
  PRIMARY KEY (transaction_id)

) ;

для customer_id у нас 1-1000, staff_id у нас 1-5 сотрудников, car_id между 1-10,000, qty 1-3

Эти поля имеют четкие требования о том, что использование диапазона может использовать функцию MySQL rand () в сочетании с формулой для генерации тех диапазонов, которые эта формула

SELECT ROUND((RAND() * (MAX - MIN)) + MIN)

Так, например, для идентификатора клиента формула

SELECT ROUND((RAND() * (1000 - 1)) + 1)

результат первой попытки

ROUND((RAND() * (1000 - 1)) + 1)  
----------------------------------
                               648

результат второй попытки

ROUND((RAND() * (1000 - 1)) + 1)  
----------------------------------
                               486

date_ordered — с 1975 по 2017, date_returned — с 1975 по 2017, для дат разница между date_ordered и date_returned должна составлять 2-3 дня.

Формула даты немного сложнее. Но он по-прежнему использует формулу ROUND((RAND() * (MAX — MIN)) + MIN)

SELECT DATE(FROM_UNIXTIME(ROUND((RAND() * (UNIX_TIMESTAMP('2017-12-31') - UNIX_TIMESTAMP('1975-01-01'))) + UNIX_TIMESTAMP('1975-01-01'))))

результат первой попытки

DATE(FROM_UNIXTIME(ROUND((RAND() * (UNIX_TIMESTAMP('2017-12-31') - UNIX_TIMESTAMP('1975-01-01'))) + UNIX_TIMESTAMP('1975-01-01'))))  
-------------------------------------------------------------------------------------------------------------------------------------
2005-08-04     

результат второй попытки

 DATE(FROM_UNIXTIME(ROUND((RAND() * (UNIX_TIMESTAMP('2017-12-31') - UNIX_TIMESTAMP('1975-01-01'))) + UNIX_TIMESTAMP('1975-01-01'))))  
-------------------------------------------------------------------------------------------------------------------------------------
1998-07-22     

Теперь мы сгенерируем одну запись данных, чтобы объединить все последние шаги.

< Сильный > Query

SELECT 
   record.customer_id
 , record.staff_id
 , record.car_id
 , record.qty
 , record.date_ordered
 , record.date_ordered + INTERVAL record.random_day DAY AS date_returned
FROM ( 
  SELECT 
     (SELECT ROUND((RAND() * (1000 - 1)) + 1)) AS customer_id
   , (SELECT ROUND((RAND() * (5 - 1)) + 1)) AS staff_id
   , (SELECT ROUND((RAND() * (10000 - 1)) + 1)) AS car_id
   , (SELECT ROUND((RAND() * (3 - 1)) + 1)) AS qty 
   , (DATE(FROM_UNIXTIME(FLOOR((RAND() * (UNIX_TIMESTAMP('2017-12-31') - UNIX_TIMESTAMP('1975-01-01'))) + UNIX_TIMESTAMP('1975-01-01')))) ) AS date_ordered
   , (SELECT ROUND((RAND() * (3 - 2)) + 2)) AS random_day
  FROM 
   DUAL
)
 record

результат первой попытки

customer_id  staff_id  car_id     qty  date_ordered  date_returned  
-----------  --------  ------  ------  ------------  ---------------
        633         2    5553       3  2011-11-21    2011-11-24   

результат второй попытки

customer_id  staff_id  car_id     qty  date_ordered  date_returned  
-----------  --------  ------  ------  ------------  ---------------
        300         4    2380       2  2010-08-21    2010-08-23     

< Сильный > Процедура

DELIMITER $$

CREATE
    PROCEDURE generate_random_data_car_transaction(IN numberOfRows INT)

    BEGIN
       DECLARE counter INT;
       SET counter = 1;

       WHILE (counter <= numberOfRows) DO
         INSERT INTO 
           car_transaction
         (
             customer_id
           , staff_id
           , car_id
           , qty
           , date_ordered
           , date_returned
         )

         SELECT 
              record.customer_id
            , record.staff_id
            , record.car_id
            , record.qty
            , record.date_ordered
            , record.date_ordered + INTERVAL record.random_day DAY AS date_returned
           FROM ( 
              SELECT 
                (SELECT ROUND((RAND() * (1000 - 1)) + 1)) AS customer_id
              , (SELECT ROUND((RAND() * (5 - 1)) + 1)) AS staff_id
              , (SELECT ROUND((RAND() * (10000 - 1)) + 1)) AS car_id
              , (SELECT ROUND((RAND() * (3 - 1)) + 1)) AS qty 
              , (DATE(FROM_UNIXTIME(FLOOR((RAND() * (UNIX_TIMESTAMP('2017-12-31') - UNIX_TIMESTAMP('1975-01-01'))) + UNIX_TIMESTAMP('1975-01-01')))) ) AS date_ordered
              , (SELECT ROUND((RAND() * (3 - 2)) + 2)) AS random_day
              FROM 
                DUAL
           )
             record;  

         SET counter = counter + 1;
       END WHILE;
    END$$

DELIMITER ;

Порядок звонка

CALL generate_random_data_car_transaction(500000);

&lt; Сильный &gt; Query

SELECT * FROM car_transaction

&lt; Сильный &gt; Результат

transaction_id  customer_id  staff_id  car_ID     QTY  date_ordered  date_returned  
--------------  -----------  --------  ------  ------  ------------  ---------------
             1          757         2    2621       2  1982-03-10    1982-03-13     
             2          818         1     368       3  1989-06-06    1989-06-08     
             3           47         2    8538       2  2009-09-30    2009-10-02     
             4          670         2    4597       2  2005-03-20    2005-03-22     
             5          216         2    7651       3  2000-10-08    2000-10-10     
             6          502         2    1364       2  1978-03-28    1978-03-30     
             7          204         2    1910       2  2009-03-17    2009-03-20     
             8          398         2    3934       1  2013-07-02    2013-07-04     
             9          474         1    9286       2  1991-08-06    1991-08-09     
            10          976         1     724       2  2000-05-09    2000-05-12     
...
...
...
        499990           20         5    6595       2  1990-05-01    1990-05-03     
        499991          839         1    7315       2  1989-12-05    1989-12-07     
        499992           14         3    1274       2  1987-11-12    1987-11-14     
        499993          539         2    5422       1  1994-06-24    1994-06-26     
        499994          728         5    7441       3  2000-05-12    2000-05-15     
        499995          512         3    4039       2  1978-02-03    1978-02-06     
        499996          732         5    2599       2  1990-01-11    1990-01-14     
        499997          304         5    6098       2  2011-11-25    2011-11-27     
        499998          818         2    8196       2  1984-01-14    1984-01-16     
        499999          617         5    8160       2  2016-03-15    2016-03-18     
        500000          864         3    7837       2  1980-01-13    1980-01-15  


2

Raymond Nijland
22 Окт 2017 в 16:38

Если бы это был мой проект, я бы поискал в intertoobz программный пакет или веб-приложение, способное генерировать случайные тестовые данные.

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

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

Тогда я бы использовал

   LOAD DATA INFILE filename INTO car_transaction 
   COLUMNS TERMINATED BY ','
   LINES TERMINATED BY '\r\n'  /* or maybe just '\n' */
   (customer_id, staff_id, car_ID, QTY, date_ordered, date_returned)

Для захвата данных из файла (называемого filename) в таблицу.


0

O. Jones
9 Окт 2017 в 16:57

Запросы в MySQL

Reg.ru: домены и хостинг

Крупнейший регистратор и хостинг-провайдер в России.

Более 2 миллионов доменных имен на обслуживании.

Продвижение, почта для домена, решения для бизнеса.

Более 700 тыс. клиентов по всему миру уже сделали свой выбор.

Перейти на сайт-&gt;

Запросы в MySQL

Бесплатный Курс «Практика HTML5 и CSS3»

Освойте бесплатно пошаговый видеокурс

по основам адаптивной верстки

на HTML5 и CSS3 с полного нуля.

Начать->

Запросы в MySQL

Фреймворк Bootstrap: быстрая адаптивная вёрстка

Пошаговый видеокурс по основам адаптивной верстки в фреймворке Bootstrap.

Научитесь верстать просто, быстро и качественно, используя мощный и практичный инструмент.

Верстайте на заказ и получайте деньги.

Получить в подарок-&gt;

Запросы в MySQL

Бесплатный курс «Сайт на WordPress»

Хотите освоить CMS WordPress?

Получите уроки по дизайну и верстке сайта на WordPress.

Научитесь работать с темами и нарезать макет.

Бесплатный видеокурс по рисованию дизайна сайта, его верстке и установке на CMS WordPress!

Получить в подарок-&gt;

*Наведите курсор мыши для приостановки прокрутки.

В этом уроке мы поговорим о следующих моментах, касающихся работы с БД MySQL: вы узнаете, как составлять сложные запросы, как использовать агрегатные функции, объединения таблиц и как оценивать производительность запросов.

Связи в БД

Связи в БД — это ассоциативное отношение между сущностями (таблицами). В первую очередь связи позволяют избегать избыточности данных.

Избыточность же — это переполнение таблиц повторяющимися данными.

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

Пример:

Запросы в MySQL

Таблица User_docs подчинена таблице Users, поэтому в ней есть ссылка на таблицу Users (user_id_ref).

У одного пользователя может быть как один, так и много документов. Поэтому мы выносим документы в отдельную таблицу, чтобы не повторялись данные по самому пользователю. Связь таблиц User и User_docs — “один-ко-многим”.

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

Запрос из двух таблиц

Функциональность MySQL не ограничивается запросом вида SELECT * FROM table. Это самый простой запрос. Такого запроса достаточно, если весь необходимый набор данных содержится в одной таблице. Но мы учимся правильно проектировать БД, поэтому и запросы у нас будут несколько сложнее и функциональнее.

Предлагаю данный момент разобрать на примерах Интернет-каталога.

Допустим, у нас задача, реализация каталога продукции в сети Интернет. Что для этого нужно сделать? Для начала спроектируем базу данных. Для этого нужно определиться с основными сущностями будущей БД. Первая и основная сущность — это Продукт. Создадим таблицу Products:

CREATE TABLE Products (
  Product_id      INT(10) auto_increment,
  Group_id_ref    INT(10),
  Product_name    CHAR (128),
  Product_desc    TEXT,
  Product_articul CHAR(32),
  Product_price   DECIMAL(14,2),
  PRIMARY KEY (product_id)
);

В этой таблице мы будем хранить наши продукты. Как вы заметили, я заранее добавил в таблицу поле Group_id_ref. Это поле привязывает продукт к конкретной группе. Создадим таблицу групп товаров:

CREATE TABLE Product_groups (
  Group_id 	 INT(10) auto_increment,
  Group_name CHAR(128),
  Group_desc TEXT,
  PRIMARY KEY (Group_id)
);

Кроме того, часто встречается ситуация, когда товары имеют дополнительные свойства, такие как Цвет, Размер и пр.

Добавим таблицу Colors:

CREATE TABLE Colors (
  Color_id 	 INT(10) auto_increment,
  Color_name CHAR(64),
  Color_desc TEXT,
  PRIMARY KEY (Color_id)
);

И таблицу Sizes (Размеры):

CREATE TABLE Sizes (
  Size_id   INT(10) auto_increment,
  Size_name	CHAR(64),
  Size_desc TEXT,
  PRIMARY KEY (Size_id)
);

Теперь мы можем хранить все наши данные по Продукту. Заполним таблицы тестовыми данными.

INSERT INTO Product_groups VALUES ('', 'Мужские костюмы', 'Костюмы, тройки, Смокинги');
INSERT INTO Colors VALUES ('', 'Черный', 'Узор в елочку');
INSERT INTO Colors VALUES ('', 'Белый', 'Белоснежный');

INSERT INTO Sizes VALUES ('', '48', '48 - российский');
INSERT INTO Sizes VALUES ('', '50', '50 - российский');

INSERT INTO Products
  VALUES ('',
          1,
         'Костюм «DS221»',
         'Элегантный костюм, подходит как для работы, так и для вечернего убранства',
         'Артикул_1',
         12000);

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

Добавим таблицы, связывающие товары с реквизитами:

CREATE TABLE Product_values (
   Record_id 		INT(10) auto_increment,
   Product_id_ref 	INT(10),
   Value_id_ref 	INT(10),
   Value_type       INT(2), /*  Тип реквизита (1–цвет, 2–размер) */
   PRIMARY KEY (Record_id)
);

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

INSERT INTO Product_values VALUES ('', 1, 1, 1);
INSERT INTO Product_values VALUES ('', 1, 1, 2);

Теперь наш тестовый продукт имеет два реквизита: Цвет и Размер.

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

— Record_id – уникальный идентификатор нашей таблицы. В прошлой статье я указывал на необходимость этого поля.

— Product_id_ref – ссылка на продукт. Собственно “_ref” и указывает на то, что это ссылка — reference. Идентификатор товара в таблице Products (мы учимся связывать именно с помощью идентификаторов).

— Value_id_ref – Ссылка на реквизиты товара.

— Value_type – Тип реквизита. 1- цвет, 2- размер и пр., если у вас таковые будут.

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

SELECT * FROM  Product_groups

Тут все просто. При помощи Group_id мы формируем ссылку на список товаров в группе. Формировать ссылку можно как в запросе, так и в скрипте, на котором написан ваш каталог.

SELECT p.product_id,
       p.product_name,
       p.product_desc,
       p.product_price,
       g.group_name
  FROM Products p, Product_groups g
 WHERE p.group_id_ref = g.group_id

Для получения списка товаров в конкретной группе добавляем
   AND g.group_id = 1 /*Идентификатор группы*/

Результат выборки выглядит так:

Запросы в MySQL

В каталоге на сайте такую выборку можно использовать в списке товаров. Product_id используем для формирования ссылки на конкретный товар.

Для конкретного товара запрос будет похожим, за исключением того, что мы укажем p.Product_id = 1.

Немного поясню, что такое «р.» в данном запросе. Для СУБД запрос вида:

SELECT product_name
    FROM Products WHERE product_id = 1

Понимается как:

SELECT Products.product_name
   FROM Products WHERE Products.product_id = 1

То есть всегда поле указывается с таблицей. В принципе, имя таблицы можно не писать, если поля ВО ВСЕХ(!) таблицах запроса именуются по-разному.

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

...FROM Products p, Product_groups g...

В этом случае p – это Products, а g – это Product_groups. Теперь в запросе нет необходимости писать имя таблицы целиком, достаточно описать только алиас.

SELECT p.product_name
   FROM Products p WHERE p.product_id = 1

Внимание! В громоздких запросах алиасы значительно ускоряют написание. Так же такой подход к написанию запроса более корректен.

Итак, для конкретного товара запрос будет таковым:

SELECT p.product_id,
       p.product_name,
       p.product_desc,
       p.product_price,
       g.group_name
  FROM Products p, Product_groups g
 WHERE p.product_id = 1
   AND p.group_id_ref = g.group_id

Теперь получим реквизиты товара. Список расцветок получаем запросом:

SELECT c.color_name, c.color_id, c.color_desc
  FROM Product_values v, Colors c
 WHERE v.product_id_ref = 1        /* ид товара            */
   AND c.color_id = v.value_id_ref /* ссылка на расцветку  */
   AND v.value_type = 1            /* тип реквизита цвет   */

Результат:

Запросы в MySQL

Подобным запросом получим и размеры.

SELECT s.size_name, s.size_id, s.size_desc
  FROM Product_values v, Sizes s
 WHERE v.product_id_ref = 1        /* ид товара            */
   AND s.size_id = v.value_id_ref  /* ссылка на размер     */
   AND v.value_type = 1            /* тип реквизита размер */

Немного поясню запрос.

v.product_id_ref = 1 — мы ищем записи в таблице реквизитов по идентификатору нашего товара.

v.value_type = 1 — указываем тип реквизита. С типами нужно заранее определиться и, при добавлении товара, добавлять реквизит с соответствующим типом.

s.size_id = v.value_id_ref — объединяем таблицы реквизитов и размеров по идентификатору реквизита. Делается это для того, чтобы по id получить наименование и описание реквизита.

Запросы с JOIN

JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в раздел FROM операторов SELECT, UPDATE или DELETE. Используется при связке двух или более таблиц.

Пример:

SELECT c.color_name, c.color_id, c.color_desc
  FROM Product_values v
       JOIN Colors c ON c.color_id = v.value_id_ref
 WHERE v.product_id_ref = 1        /* ид товара            */
       AND v.value_type = 1        /* тип реквизита цвет   */

Такое объединение выдаст нам набор записей, в котором данные таблицы Colors присутствуют в таблице Product_values. То есть только те записи, которые удовлетворяют условию c.color_id = v.value_id_ref.

Но бывают такие случаи, когда нам нужно получить все данные из одной таблицы и только те данные из второй таблицы, которые присутствуют в первой. Рассмотрим на примере.

Допустим, для товаров мы будем хранить фото. Создадим таблицу для фотографий.

CREATE TABLE Product_photos (
  photo_id INT(10) auto_increment,
  product_id_ref INT(10),
  photo_path CHAR(128),       /* Имя файла фото           */
  is_main INT(1),             /* Основное — 1, иначе - 0  */
  PRIMARY KEY (photo_id)
);

Представим условие, что не у всех товаров есть фото и напишем запрос для получения списка товаров с фото.

SELECT p.product_id,
       p.product_name,
       p.product_desc,
       ph.photo_path
  FROM Products p
       LEFT JOIN Product_photos ph
       ON ph.product_id_ref = p.product_id AND ph.is_main = 1

Результат выборки следующий:

Запросы в MySQL

Как мы видим, у товара нет фотографии. NULL означает пусто.

Но, когда мы в скриптовом языке (PHP и пр.) будем выводить список, и в тег img попадет пустое значение, фото в браузере будет потеряно.

Модифицируем запрос для того, чтобы избежать этого:

SELECT p.product_id,
       p.product_name,
       p.product_desc,
       IFNULL(ph.photo_path, 'empty.jpg') photo_path
  FROM Products p
       LEFT JOIN Product_photos ph
       ON ph.product_id_ref = p.product_id AND ph.is_main = 1

IFNULL обрабатывает как раз значение NULL. Если значение пустое, можем подставить свое значение. В данном случае мы подставим «empty.jpg». Для корректного отображения на странице добавим на сайт изображение empty.jpg и теперь мы имеем красивый список.

Внимание! Старайтесь всегда обрабатывать значения NULL. Не стоит такого рода логику обрабатывать на клиентском приложении, запросами она обрабатывается значительно легче.

Теперь непосредственно про LEFT JOIN. Так называемое «левое объединение» выводит все данные основной таблицы и только те данные второй, которые удовлетворяют условию блока ON.

Есть также RIGHT и FULL JOIN. RIGHT, по сути, аналогичен LEFT, только запрос выведет все данные второй таблицы и те записи первой, которые удовлетворяют условию блока ON.

Можно всегда использовать LEFT, только менять местами таблицы.

FULL JOIN выведет все данные обеих таблиц, но практическую реализацию подобного запроса встретишь довольно редко.

Агрегатные функции

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

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

Запросы могут производить обобщенное групповое значение полей точно так же, как и значение одного поля. Это делается с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Список этих функций:

COUNT — выводит количество полей, которые выбрал запрос;
SUM — выводит арифметическую сумму всех выбранных значений данного поля;
MAX — выводит наибольшее из всех выбранных значений данного поля;
MIN — выводит наименьшее из всех выбранных значений данного поля;
AVG — выводит усреднение всех выбранных значений данного поля.

При написании запросов с агрегатными функциями, необходимо научиться правильным образом организовать группировку (GROUP BY).

Пример запроса с группировкой:

SELECT COUNT(p.product_id) cnt,
       g.group_name
  FROM Products p, Product_groups g
 WHERE p.group_id_ref = g.group_id
 GROUP BY p.group_id_ref

Запрос выведет нам список групп и количество товаров в каждой:

Запросы в MySQL

Остальные агрегатные функции работают аналогично, и запросы выглядят идентично:

SELECT SUM(p.product_price) summ,
       g.group_name
  FROM Products p, Product_groups g
 WHERE p.group_id_ref = g.group_id
 GROUP BY p.group_id_ref

Запрос выведет нам список групп и общую стоимость товаров в каждой.

Внимание! Агрегатные функции используются только в блоке SELECT. Если мы хотим добавить агрегатную функцию в блок WHERE, нужно использовать команду HAVING.

Пример:

SELECT g.group_name
  FROM Products p, Product_groups g
HAVING COUNT(*) > 1
 GROUP BY p.group_id_ref

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

Оценка производительности запросов

Тут все настолько просто, насколько сложно. Для оценки производительности необходимо перед запросом добавить EXPLAIN EXTENDED.

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

EXPLAIN EXTENDED
SELECT p.product_id,
       p.product_name,
       p.product_desc,
       ph.photo_path
  FROM Products p
       LEFT JOIN Product_photos ph
       ON ph.product_id_ref = p.product_id AND ph.is_main = 1

Результат выполнения:

Запросы в MySQL

Я преднамеренно убрал все индексы из запроса, чтобы план показал, что запрос неэффективен.

Значения полей possible_keys, key, key_len и ref не заполнены. Такой результат нас не устраивает. Поэтому добавим индексы на колонки Product_photos.product_id_ref и Products.product_id.

Внимание! Не стоит перегружать таблицу индексами. От того, что таблица будет вся проиндексирована, запрос не будет выполняться быстрее. К тому же размер индекса будет сопоставим с размерами таблицы.

Итог

В данной статье мы изучили:

— Связи в БД
— Запросы из двух и более таблиц
— Запросы с JOIN
— Агрегатные функции
— Оценку производительности запросов

Текущего набора знаний вполне достаточно, чтобы делать большие интернет проекты с использованием БД. Для себя вы можете доработать БД индексами и триггерами.

Материал подготовил Владимир Миняйлов специально для сайта CodeHarmony.ru

Исходники:

CREATE TABLE Products (
  Product_id   	INT(10) auto_increment,
  Group_id_ref 	INT(10),
  Product_name 	CHAR (128),
  Product_desc 	TEXT,
  Product_articul 	CHAR(32),
  Product_price 	DECIMAL(14,2),
  PRIMARY KEY (product_id),
  INDEX (Group_id_ref)
);

CREATE TABLE Product_groups (
  Group_id 	INT(10) auto_increment,
  Group_name 	CHAR(128),
  Group_desc 	TEXT,
  PRIMARY KEY (Group_id)
);

CREATE TABLE Colors (
  Color_id 	INT(10) auto_increment,
  Color_name 	CHAR(64),
  Color_desc 	TEXT,
  PRIMARY KEY (Color_id)
);

CREATE TABLE Sizes (
  Size_id 		INT(10) auto_increment,
  Size_name 		CHAR(64),
  Size_desc 		TEXT,
  PRIMARY KEY (Size_id)
);

CREATE TABLE Product_values (
   Record_id 		INT(10) auto_increment,
   Product_id_ref 	INT(10),
   Value_id_ref 	INT(10),
   Value_type INT(2), /*  Тип реквизита (1–цвет, 2–размер) */
   PRIMARY KEY (Record_id),
   INDEX(product_id_ref)
);

CREATE TABLE Product_photos (
  photo_id       INT(10) auto_increment,
  product_id_ref INT(10),
  photo_path     CHAR(128),      /* Имя файла фото           */
  is_main       INT(1),          /* Основное — 1, иначе - 0  */
  PRIMARY KEY (photo_id),
  INDEX(product_id_ref)
);

/* Группы товаров */
INSERT INTO Product_groups VALUES ('', 'Мужские костюмы', 'Костюмы, тройки, Смокинги');
/* Расцветки */
INSERT INTO Colors VALUES ('', 'Черный', 'Узор в елочку');
INSERT INTO Colors VALUES ('', 'Белый', 'Белоснежный');
/* Размеры */
INSERT INTO Sizes VALUES ('', '48', '48 - российский');
INSERT INTO Sizes VALUES ('', '50', '50 - российский');
/* Товары */
INSERT INTO Products
  VALUES ('',
          1,
         'Костюм «DS221»',
         'Элегантный костюм, подходит как для работы, так и для вечернего убранства',
         'Артикул_1',
         12000);
/* Реквизиты товаров */
INSERT INTO Product_values VALUES ('', 1, 1, 1);
INSERT INTO Product_values VALUES ('', 1, 1, 2);

P.S. Хотите углубить свои знания и навыки? Присмотритесь к премиум-урокам по различным аспектам сайтостроения, включая SQL и работу с БД, а также к бесплатному курсу по созданию своей CMS-системы на PHP с нуля.

Запросы в MySQL

Запросы в MySQL

Понравился материал и хотите отблагодарить?
Просто поделитесь с друзьями и коллегами!

Смотрите также:

PHP: Получение информации об объекте или классе, методах, свойствах и наследовании

PHP: Получение информации об объекте или классе, методах, свойствах и наследовании

CodeIgniter: жив или мертв?

CodeIgniter: жив или мертв?

Функции обратного вызова, анонимные функции и механизм замыканий

Функции обратного вызова, анонимные функции и механизм замыканий

Применение функции к каждому элементу массива

Применение функции к каждому элементу массива

Слияние массивов. Преобразование массива в строку

Слияние массивов. Преобразование массива в строку

Деструктор и копирование объектов с помощью метода __clone()

Деструктор и копирование объектов с помощью метода __clone()

Эволюция веб-разработчика или Почему фреймворк - это хорошо?

Эволюция веб-разработчика или Почему фреймворк — это хорошо?

Магические методы в PHP или методы-перехватчики (сеттеры, геттеры и др.)

Магические методы в PHP или методы-перехватчики (сеттеры, геттеры и др.)

PHP: Удаление элементов массива

PHP: Удаление элементов массива

Ключевое слово final (завершенные классы и методы в PHP)

Ключевое слово final (завершенные классы и методы в PHP)

50 классных сервисов, программ и сайтов для веб-разработчиков

50 классных сервисов, программ и сайтов для веб-разработчиков

Наверх

Скрипт заполнения бд тестовыми данными.

insert into Drivers(Full_Name, Birth_Date, Passport_SN, Medical_Insurance_Number, Base_Salary, Percent_From_Tickets, Driving_License_Number, Cathegories)

values(‘Бородач Равшан Игоревич’, date ’21-12-1977′, 6412789874, 5418547896, 5500.00, 15, ’73МО432354′, ‘B, C’);

insert into Drivers(Full_Name, Birth_Date, Passport_SN, Medical_Insurance_Number, Base_Salary, Percent_From_Tickets, Driving_License_Number, Cathegories)

values(‘Трубач Ибрагим Афанасьевич’, date ‘1-6-71′, 1112289475, 7475589091, 5500.00, 15, ’13МО124189’, ‘C’);

insert into Cars(Brand_Name, Model, Vin, Registration_Number, Date_Of_Release, Date_Last_Techcheck, Techcheck_Period, Fuel_On_100km, Places)

values(‘ГАЗ’, ‘3221’, ‘12345678912345678’, ‘Б432ВТ64’, ‘8-3-2011’, ‘8-3-2011’, 1095, 15.5, 13);

insert into Cars(Brand_Name, Model, Vin, Registration_Number, Date_Of_Release, Date_Last_Techcheck, Techcheck_Period, Fuel_On_100km, Places)

values(‘ГАЗ’, ‘3221’, ‘323T56789A2345678’, ‘Б423ВТ64’, ‘8-3-2011’, ‘8-3-2011’, 1095, 15.5, 13);

insert into Service(Driver_ID, Car_ID, Time, Fuel_Price, Fuel_Quantity, Oil_Price, Oil_Quantity, Service_expenses, Service_description)

values(6412789874, ‘12345678912345678’, timestamp ‘2011-12-14 06:30’, 26.15, 30, 400, 4, 0, null);

insert into Service(Driver_ID, Car_ID, Time, Fuel_Price, Fuel_Quantity, Oil_Price, Oil_Quantity, Service_expenses, Service_description)

values(1112289475, ‘323T56789A2345678’, timestamp ‘2011-12-14 06:40’, 26.15, 30, 0, 0, 0, null);

insert into Routes(Number, Length)

insert into Routes(Number, Length)

insert into Trips(Driver_ID, Car_ID, Route_ID, Time_Start, Time_Stop, Transportet_Passengers, Ticket_Price)

values(6412789874, ‘12345678912345678’, 61, timestamp ‘2011-12-14 07:00’, timestamp ‘2011-12-14 08:02’, 21, 12);

insert into Trips(Driver_ID, Car_ID, Route_ID, Time_Start, Time_Stop, Transportet_Passengers, Ticket_Price)

values(1112289475, ‘323T56789A2345678’, 31, timestamp ‘2011-12-14 07:15’, timestamp ‘2011-12-14 08:41’, 32, 12);

insert into Trips(Driver_ID, Car_ID, Route_ID, Time_Start, Time_Stop, Transportet_Passengers, Ticket_Price)

values(1112289475, ‘323T56789A2345678’, 31, timestamp ‘2011-12-14 08:50’, timestamp ‘2011-12-14 10:03’, 16, 12);

Быстрое наполнение тестовых таблиц в MySQL

Представим, что вы создаете новое приложение и вам нужно протестировать его на большом объеме данных (volume testing). В этом случае вы можете взять уже готовые данные, или же подготовить их самостоятельно. Если у вас есть набор данных для тестов достаточного объема – это просто замечательно, но чаще всего данных нужного объема у вас не будет и вам будет нужен способ для быстрого их создания. Ниже будут перечислены три способа создания больших наборов данных простых типов (чисел, слов, дат).

Числа

Создавать большие наборы числовых данных совсем не сложно.

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

Данный способ намного более быстрый, нежели прямая вставка 1’000’000 строк. Мы вставляем в таблицу всего одну строку, и потом дублируем таблицу 20 раз, пока не получим 1’048’576 строк (2 20 ). Эта операция занимает менее 8 секунд на ноутбуке автора, который намного менее мощный, нежели средний сервер (на момент написания статьи – 2006г. // hudson). Даже если вы не хотите использовать хранимую процедуру, вы можете вручную вставить 1 строку и выполнить 20 раз следующий запрос:

Эта процедура не займет у вас больше 30 секунд.

Слова

Если вам нужно работать с большим объемом уникальных строковых данных, вы опять таки, можете написать программу на любом языке, однако это будет совсем не быстро (процесс вставки достаточно медленный). Наиболее быстрый способ – загрузить готовый список слов из файла. Все Unix системы содержат списки слов – от нескольких тысяч, до полумиллиона. Если вдруг у вас такого не оказалось, вы можете скачать его из множества доступных источников или собрать самостоятельно (для начала можете посмотреть здесь: ftp://ftp.cerias.purdue.edu/pub/dict/ или здесь ftp://ftp.ox.ac.uk/pub/wordlists/).

Быстро, не правда ли? Но постойте-ка, у нас пока что есть только пол миллиона записей (ну… чуть больше). Так как нам нужны уникальные слова, мы можем попросить базу данных, изменить порядок букв в уже существующих (reverse()):

Вот так вот! Но мы все еще можем сомневаться, уникальны ли эти слова, так как изменение порядка букв одного слова может превратить его в другое (например mood doom). Т.о. чтобы считать нашу задачу завершенной, нужно добавить UNIQUE индекс с опцией IGNORE, что позволит исключить дубликаты:

Вот так – миллион слов, не напрягаясь )

Даты

Наконец, давайте посмотрим, как можно быстро и просто создавать большие наборы дат. Фактически, миллион разных дат вам вряд ли понадобится, т.к. миллион дней это более 2700 лет. Т.о. только даты будут покрывать диапазон от 1000 до 10000 дней, вряд ли больше. Если же вам нужен миллион записей, то впору поговорить не о DATE а о DATETIME с интервалами в часы, минуты или даже секунды. Никто не запрещает вам использовать эту технику для создания сотни уникальных DATE, но при этом иметь в таблице что-то около миллиона записей. Итак, если мы хотим создать записи с минутным интервалом то нужно выполнить следующий код:

Выглядит знакомо, не правда ли? ) Неудивительно, ведь ту же технику мы использовали для заполнения таблицы с числами. Хотя в отличие от чисел, здесь мы использовали число записей в таблице для вычисления интервала в минутах между существующими записями и теми записями, которые будут добавлены в этой итерации. Но мы также дублируем таблицу 20 раз, чтобы получить 1’000’000 записей.

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

Заключение

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

Источник

Как заполнить базу данных MS SQL разнородными случайными данными или 17 часов ожидания

Введение

Несколько дней назад я получил задачу по заполнению базы данных под управлением MS SQL Server случайными данными. Причем, вся реализация должна быть выполнена только средствами T-SQL. После долго поиска подобных решений на ресурсах пришел к выводу — придется делать самому и принялся за дело. Не являясь (до последнего времени) знатоком T-SQL, а имея лишь набор знаний из курса «Базы данных» университета, реализация получилась очень «костылявая» и медленная(основная проблема), но работающая.

Основная цель данной статьи обсудить с хабра сообществом возможность оптимизации решения, либо его Ctrl+A и Shift + Del с ссылкой на уже готовую реализацию.

Реализация

Я использовал один из первых попавшихся мне вариантов реализации с форума MS SQL. Процедура получает на вход длину строки, а на выходе выдает строку случайных символов типа NVARCHAR(MAX) нужного размера. В данном случая реализация не является критичной, так как не имеет серьезных временных затрат при больших объемах данных. Едем дальше.

Функция небольшая и не очень красивая (особенно место с SUBSTRING), но меня она вполне устроила своим быстродействием, так что пока оставляем ее и идем дальше.

И вот, не дойдя до «главной» процедуры мы получаем огромные временные затраты при заполнении внешнего ключа таблицы данными из найденной родительской таблицы. Если данный поиск заметь подстановкой случайных чисел в заданном диапозоне производительность резко возрастает. Возможно дело в SELECT’e из системной таблице и случайной сортировки. Для сравнения: запись 1 млн. строк в таблицу без FK занимает около 20 мин, запись 1 млн. строк в таблицу с FK занимает больше 17 часов. Для справки, запись одного миллиона строк чистым INSERT’ом в одно поле занимаем 6-10 сек. На текущий момент я не смог придумать ничего более оптимального, что и послужило толчком к написанию этой статьи, но об этом в заключении.

Данная процедура является «относительно» не затратной по времени хотя и лезет в системные таблицы чтобы получить структуру пришедшей на вход таблицы, но содержит в себе несколько явных слабых мест. Например прыжок через первый элемент таблицы в надежде на то, что именно он являет PK.

Источник

Как разработать систему автоматического тестирования?

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

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

Какой процесс тестирования подходит?

В результате длительного размышления был выработан простой трёхэтапный процесс:

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

8 ответов

Вы также можете использовать хранимую процедуру. В качестве примера рассмотрим следующую таблицу:

CREATE TABLE your_table (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, val int);

Затем вы можете добавить хранимую процедуру следующим образом:

DELIMITER $$
CREATE PROCEDURE prepare_data()
BEGIN
  DECLARE i INT DEFAULT 100;

  WHILE i < 100000 DO
    INSERT INTO your_table (val) VALUES (i);
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

Когда вы его вызовете, у вас будет 100 тыс. записей:

CALL prepare_data();


87

Daniel Vassallo
22 Сен 2010 в 08:45

Для клонирования нескольких строк (дублирование данных) вы можете использовать

DELIMITER $$
CREATE PROCEDURE insert_test_data()
BEGIN
  DECLARE i INT DEFAULT 1;

  WHILE i < 100000 DO
    INSERT INTO `table` (`user_id`, `page_id`, `name`, `description`, `created`)
    SELECT `user_id`, `page_id`, `name`, `description`, `created`
    FROM `table`
    WHERE id = 1;
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL insert_test_data();
DROP PROCEDURE insert_test_data;


18

michalzuber
24 Июн 2013 в 09:49

Вот это решение с чистой математикой и sql:

create table t1(x int primary key auto_increment);
insert into t1 () values (),(),();

mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 1265 rows affected (0.01 sec)
Records: 1265  Duplicates: 0  Warnings: 0

mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 2530 rows affected (0.02 sec)
Records: 2530  Duplicates: 0  Warnings: 0

mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 5060 rows affected (0.03 sec)
Records: 5060  Duplicates: 0  Warnings: 0

mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 10120 rows affected (0.05 sec)
Records: 10120  Duplicates: 0  Warnings: 0

mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 20240 rows affected (0.12 sec)
Records: 20240  Duplicates: 0  Warnings: 0

mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 40480 rows affected (0.17 sec)
Records: 40480  Duplicates: 0  Warnings: 0

mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 80960 rows affected (0.31 sec)
Records: 80960  Duplicates: 0  Warnings: 0

mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 161920 rows affected (0.57 sec)
Records: 161920  Duplicates: 0  Warnings: 0

mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 323840 rows affected (1.13 sec)
Records: 323840  Duplicates: 0  Warnings: 0

mysql> insert into t1 (x) select x + (select count(*) from t1) from t1;
Query OK, 647680 rows affected (2.33 sec)
Records: 647680  Duplicates: 0  Warnings: 0


5

Daniil Iaitskov
20 Июл 2017 в 20:03

Если вы хотите больше контролировать данные, попробуйте что-то вроде этого (в PHP):

<?php
$conn = mysql_connect(...);
$num = 100000;

$sql = 'INSERT INTO `table` (`col1`, `col2`, ...) VALUES ';
for ($i = 0; $i < $num; $i++) {
  mysql_query($sql . generate_test_values($i));
}
?>

Где функция generate_test_values ​​вернет строку в формате «(‘val1’, ‘val2’, …)». Если это займет много времени, вы можете объединить их, чтобы не делать так много вызовов БД, например:

for ($i = 0; $i < $num; $i += 10) {
  $values = array();
  for ($j = 0; $j < 10; $j++) {
    $values[] = generate_test_data($i + $j);
  }
  mysql_query($sql . join(", ", $values));
}

Будет выполнять только 10000 запросов, каждый из которых добавляет 10 строк.


4

gmarcotte
22 Сен 2010 в 08:39

Попробуйте filldb

Вы можете либо опубликовать свою схему, либо использовать существующую схему и создать фиктивные данные, экспортировать их с этого сайта и импортировать в свою базу данных.


1

ganesh konathala
2 Апр 2019 в 11:13

Мне очень нравится утилита mysql_random_data_loader от Percona, вы можете найти более подробную информацию о ней здесь.

Mysql_random_data_loader — это утилита, которая подключается к базе данных mysql и заполняет указанную таблицу случайными данными. Если в таблице присутствуют внешние ключи, они также будут корректно заполнены.

У этой утилиты классная фишка, скорость генерации данных можно ограничить.

Например, чтобы сгенерировать 30 000 записей, в таблице sakila.film_actor со скоростью 500 записей в секунду нужна следующая команда

mysql_random_data_load sakila film_actor 30000 --host=127.0.0.1 --port=3306 --user=my_user --password=my_password --qps=500 --bulk-size=1

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


1

Ivan Gusev
9 Окт 2020 в 18:07

create table mydata as select * from information_schema.columns;
insert into mydata select * from mydata;
-- repeating the insert 11 times will give you at least 6 mln rows in the table.

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

Первая строка создает таблицу с именем mydata и генерирует расположение столбцов из information_schema, в котором хранится информация о вашем сервере MYSQL, и в этом случае она извлекается из information_schema.columns, что позволяет создаваемой таблице иметь всю информацию о столбцах, необходимую для создания не только таблицы, но и всех столбцов, которые вам понадобятся автоматически, что очень удобно.

Вторая строка начинается с оператора Insert, который теперь будет нацелен на эту новую таблицу с именем mydata и вставит данные Information_schema в таблицу. Последняя строка — это просто комментарий, предлагающий вам запустить скрипт несколько раз, если вы хотите сгенерировать больше данных.

Наконец, в заключение, в моем тестировании одно выполнение этого скрипта сгенерировало 6 956 строк данных. Если вам нужен быстрый способ создания некоторых записей, это неплохой метод. Однако для более продвинутого тестирования вы можете захотеть ALTER включить в таблицу первичный ключ, который автоматически увеличивается, чтобы у вас был уникальный индекс, поскольку база данных без первичного ключа — это печальная база данных. Это также имеет тенденцию иметь непредсказуемые результаты, поскольку могут быть повторяющиеся записи. При всем при этом я хотел дать некоторое представление об этом коде, потому что я нашел его полезным, и я думаю, что другие тоже могли бы, если бы только они потратили время на объяснение того, что он делает. Большинство людей не являются поклонниками выполнения кода, потому что они понятия не имеют, что он будет делать, даже из надежного источника, поэтому, надеюсь, кто-то еще нашел это полезным, как и я. Я предлагаю это не как «ответ», а как еще один источник информации, который поможет обеспечить некоторую материально-техническую поддержку приведенному выше ответу.


1

easleyfixed
14 Май 2021 в 19:24

Это более эффективная модификация ответа @michalzuber. Единственная разница заключается в удалении WHERE id = 1, так что вставки могут накапливаться при каждом запуске.

Количество произведенных записей будет n^2;

Таким образом, для 10 итераций 10^2 = 1024 записи. Для 20 итераций 20^2 = 1048576 записей и так далее.

DELIMITER $$
CREATE PROCEDURE insert_test_data()
BEGIN
  DECLARE i INT DEFAULT 1;

  WHILE i <= 10 DO
    INSERT INTO `table` (`user_id`, `page_id`, `name`, `description`, `created`)
    SELECT `user_id`, `page_id`, `name`, `description`, `created`
    FROM `table`;
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL insert_test_data();
DROP PROCEDURE insert_test_data;


0

Bernard Wiesner
14 Июл 2021 в 09:57

6 ответов

Ну, я подумал, что вытащу палец и напишу себе легкий генератор данных:

Для любой таблицы сценарий создаст одну запись с некоторыми произвольными значениями для типов; int, varchar, nvarchar, smalldatetime и бит. Оператор case можно заменить функцией. Он не будет перемещаться по зависимостям, но пропустит все заполненные столбцы.

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

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

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

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

Кроме того, вы можете экспортировать в SQL (или несколько других форматов) и использовать созданные данные в любое время для повторного заполнения базы данных.

Существует программа от Red Gate Software, которая сделает это за вас. Он называется Генератором данных SQL.

Некоторые разновидности Visual Studio имеют встроенную генерацию данных. Если вы используете в нем проекты баз данных, вы можете создавать планы генерации данных. Вот статья MSDN

Я использовал следующий способ, которым он в основном копирует данные из себя, данные растут экспоненциально с каждым выполнением. Ключевым моментом является то, что сначала у вас должны быть образцы данных, а также вы должны выполнить запрос, например, у меня было 327680 строк данных, когда я начал с 10 строк данных. Выполняя запрос всего 16 раз. Выполните еще раз, и я получу 655360 строк данных!

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

1. Создайте таблицу:

2. Объявления переменных

3.Установить вовремя:

4.Заполните таблицу:

5.Подготовка ценностей

6. Написать заявление о вставке:

Источник

5 ответов

Лучший ответ

Отличный SQL Fiddle предлагает именно то, что я хотел: Text to DDL


0

Peter Lang
22 Май 2012 в 08:54

Я бы предложил использовать phpMyAdmin. Он предоставляет довольно простой в использовании интерфейс для одновременной вставки нескольких строк.


1

Jordan Ryan Moore
17 Дек 2009 в 19:27

Теперь, когда я понимаю вопрос, я могу ответить … Я бы либо сделал массовый импорт CSV / TSV, либо (что более вероятно для небольших наборов данных) загрузил пример из вопроса в Vim и повторно выразил его в операторы вставки . Я бы написал операторы создания таблицы вручную. Все это, конечно, предполагает, что мне вообще нужно создавать таблицы, поскольку в целом я отвечаю по опыту и не нуждаюсь в каких-либо реальных таблицах для игры (как сказал @gahooa в своем комментарии).


1

rmeador
17 Дек 2009 в 19:53

Вы можете сделать это за вас на странице http://www.generatedata.com/#generator. Просто укажите имена столбцов и типы данных, а также способ вывода данных (операторы SQL, HTML, Excel, XML, CSV).


1

Mohsin Sheikh Khalid
23 Сен 2010 в 07:49

Массовые вставки должны быть полезным термином для поиска.


0

DanDan
17 Дек 2009 в 19:27

Создать тестовые данные в SQL Server

Есть ли у кого-нибудь или знает SQL-скрипт, который будет генерировать тестовые данные для данной таблицы?

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

Если этого не существует, может ли кто-нибудь еще пригодиться? Если да, я вытащу палец и напишу один.

Мотивация

Зачем мы заставляем себя писать юнит-тесты? Для повышения качества разрабатываемого приложения. Чем больше разрабатываемый проект, тем больше вероятность сломать что-то, особенно во время рефакторинга. Юнит-тесты позволяют быстро проверить корректность работы компонентов системы. Но написание юнит-тестов для методов, взаимодействующих с базой данных, — это не такая и простая задача, т.к. для корректной работы теста требуется настроенное окружение, а если точнее, то:

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

Производительность

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

Как подружить юнит-тестирование с базой данных

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

Заключение

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

После того как была разработана система автоматического тестирования запросов в базу данных, писать и тестировать код, связанный с запросами в базу данных, стало намного проще. TDD-подход можно спокойно применять не только для «классических» компонентов, но и для компонентов, которые тесно взаимодействуют с базой данных. После интеграции данной системы в окружение проекта следить за состоянием качества проекта стало намного проще, т.к. некорректное поведение компонентов выявляется во время билда проекта и сразу видно всем разработчикам.

И напоследок хотелось бы узнать, какими образом происходит тестирование уровня доступа к данным на ваших проектах?

Источник