powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
25 сообщений из 35, страница 1 из 2
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39310862
Привет! В таблице MySql хранятся закрашенные области (серый цвет), как координаты левого верхнего угла и правого нижнего, т.е. ЧЕТЫРЕ числа (х1,у1,х2,у2) и поле статус=ноль - нет закраски, статус=единица - закрашено.. Как запросом в базу MySql создать новую закрашенную область, если входящие условия - размеры? Т.е. условия такие, например:

п.1 начиная слева сверху найти свободную область размером 1х1 и соответственно записать в эту ячейку статус=1
п.2 начиная слева сверху найти свободную область размером 2х2 и соответственно записать в эти ЧЕТЫРЕ ячейки статус=1

То есть для п.1 ответ должен быть 0,0,1,1 (голубой квадратик) для п.2 = 6,0,8,2 (оранжевый квадратик)

Нюанс в том, что размер сетки 2000х2000, т.е. всего 4млн квадратиков.
Пока вижу два решения:
1. каким-то сложным запросом делать INSERT внутри таблицы используя SET MIN и тд, возможно зацикливание?
2. использовать доп. таблицу (приложил пример на втором рисунке), где перечислены все 4 млн. комбинации, через left join искать не совпадающие ячейки?. но тут коллизия - пока ищу по одной таблице, во вторую уже кто-то пишет...
Коллеги, подскажите, куда хотя бы двигаться в размышлениях?

Ниже пример SQL который хранит эти закрашенные серым области.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE TABLE `m_items` (
  `id` int(11) NOT NULL,
  `x1` smallint(4) NOT NULL,
  `y1` smallint(4) NOT NULL,
  `x2` smallint(4) NOT NULL,
  `y2` smallint(4) NOT NULL,
`status` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `m_items` (`id`, `x1`, `y1`, `x2`, `y2`) VALUES
(1, 3, 0, 6, 4),
(2, 8, 0, 9, 2),
(3, 0, 1, 2, 2),
(4, 0, 3, 2, 5),
(5, 7, 3, 9, 7),
(6, 1, 6, 6, 7),
(7, 1, 8, 2, 10);
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39310863
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Олег Котейкини поле статус=ноль - нет закраски, статус=единица - закрашено.Долго искал в примере данных - не нашёл. Тыкни пальцем, что ли...

Олег Котейкинзапросом в базу MySql создать новую закрашенную область, если входящие условия - размерыНе вижу проблем. Конвертируй свой [censored] исходный формат данных во вменяемый Row-Col-Val - и запрос получится почти тривиальным.

Олег Котейкинразмер сетки 2000х2000
И так понятно, что выполнение запроса будет тяжёлым.
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39310871
Поле статус озвучил для понимания. По сути область закрашена уже если есть строка с координатами - именно такая область и красится по координатам. Т.е. по входящим данным в виде двух чисел (размеры ШхВ) нужен INSERT ЧЕТЫРЕХ значений, при этом эти значения в комплексе не должны пересечься с уже существующими.
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39316702
можно выбрать другую структуру хранения, может тогда проще будет составить скрипт, коллеги?
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39316760
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
вот запрос, который создает возможные области для закраски
из него уже инсерт сделать можно
для него нужна опорная таблица crd, в которой колонка n содержит значения возможных координат
в вашем случае 2001 строка от 0 до 2000,
для блокировки клеток чтоб в них не красили
придется делать таблицу для всех клеток
state для не закрашенных = null
это для того чтоб не покрашенные клетки можно было блокировать с помощью SELECT FOR UPDATE
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT
  x1_crd.n AS x1,
  y1_crd.n AS y1,
  x2_crd.n AS x2,
  y2_crd.n AS y2
FROM crd x1_crd, crd x2_crd, crd y1_crd, crd y2_crd
WHERE x2_crd.n - x1_crd.n = ${size} AND y2_crd.n - y1_crd.n = ${size}
AND NOT EXISTS(SELECT m.id FROM m_items m WHERE m.x1 < x2_crd.n AND m.x2 > x1_crd.n AND m.y1 < y2_crd.n AND m.y2 > y1_crd.n LIMIT 1)
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39316765
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
пожалуй в таблице 2000х2000 state не нужен только координаты клеток для блокировки
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39316932
bochkov,
ого, спасибо, щас потестирую!

опорная таблица crd - там должно быть 2000 строк или все-таки 2000*2000=4000000 строк? т.еб ВСЕ возможные сочетания координат?
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39316941
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Олег Котейкинbochkov,
ого, спасибо, щас потестирую!

опорная таблица crd - там должно быть 2000 строк или все-таки 2000*2000=4000000 строк? т.еб ВСЕ возможные сочетания координат?
Должно быть 2001 строка,
в запросе таблица сама на себя умножается
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39316970
bochkov,

что то уже минут 5 висит... ${size} - это искомый размер в виде числа?
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39316975
bochkov,

а если не кубик а прямоугольник искать?
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39316984
bochkov,

хотя вот вывело :
Отображение строк 0 - 24 (972196 всего, Запрос занял 0.8070 сек.)
Типа что-то нашло, т.е. запрос 0,8 сек? а что висело долго 5 минут?
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39316998
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
первый size - ширина, второй - высота,
первый раз запрос- реальное время,
второй запрос - из кеша
чтоб сократить время надо создать индексы
по x1 y1 x2 y2 именно в таком порядке,
возможно primary key по полю n поможет
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317002
bochkov,

насчет size я основательно тормознул!!))) погода... разобрался!!!
по n индекс создал - теперь 0,5 сек

а по поводу "...по x1 y1 x2 y2 именно в таком порядке..." - т.е. составной индекс нужен? в той таблице уже есть индекс по id - первичный автоинкремент - он тут не помогает?

а вообще получилось очень отличный вариант, соседи со стаковерфлоу неделю вместе думали и такого скоростного не получилось создать! Премного Вам благодарен!

Щас индексы составить попробую и отпишусь тут
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317006
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
очень много извлекать ему приходится
добавь в запрос limit 1 по задаче ведь нужна одна доступная область
а чтобы поиск был слева или справа order by x1_crd.n
desc-справа налево , asc слева направо
order by y1_crd.n поиск сверху вниз
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317017
bochkov,

ну да, limit 1 поставил, он ищет столько же 0,5сек, вывод на экран тормозил просто когда без лимита было.
а без order by выводит крайнее слева сверху, как и требуется! точно нужен order by ? Сейчас с ним вот так получилось Отображение строк 0 - 0 (1 всего, Запрос занял 117.4495 сек.). Получается если много результатов , по сути щас миллилон, то сначала сортирует этот миллион а потом лимит?
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317025
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
могут ускорить запрос 2 составных ключа
x1 x2 и y1 y2
чтобы реальное время смотреть
надо добавить модификатор к селектор
sql_no_cache так по моему называется
не удобно с телефона проверять
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317033
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
order by нужен
без него mysql первое попавшие
вываливает
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317055
bochkov,

с сортировкой по x и y вообще сервер слетел)) #2006 - MySQL server has gone away
составной индекс добавил...
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317074
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
ну тут уж не знаю чем помочь
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317117
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Чисто поржать...
Код: sql
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.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
mysql> -- Исходные данные
mysql> CREATE TABLE `m` (
    ->   `id` INT,
    ->   `x1` INT,
    ->   `y1` INT,
    ->   `x2` INT,
    ->   `y2` INT
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `m` (`id`, `x1`, `y1`, `x2`, `y2`) VALUES
    -> (1, 3, 0, 6, 4),
    -> (2, 8, 0, 9, 2),
    -> (3, 0, 1, 2, 2),
    -> (4, 0, 3, 2, 5),
    -> (5, 7, 3, 9, 7),
    -> (6, 1, 6, 6, 7),
    -> (7, 1, 8, 2, 10);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> -- Промежуточная таблица (но можно было добавить и поле в исходную)
mysql> CREATE TABLE p(id INT, rng GEOMETRY);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO p (id,rng) SELECT id,ST_GeomFromText(CONCAT('Polygon((',x1,' ',y1,',',x1,' ',y2,',',x2,' ',y2,',',x2,' ',y1,',',x1,' ',y1,'))')) FROM m;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> -- Временная таблица, в реальном запросе не создаётся, используется как подзапрос
mysql> CREATE TEMPORARY TABLE x(x1 int, y1 int, p GEOMETRY);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO x(x1, y1, p)
    -> SELECT x1,y1, ST_GeomFromText(CONCAT('Polygon((',x1+0.5,' ',y1+0.5,',',x1+0.5,' ',y1+1.5,',',x1+1.5,' ',y1+1.5,',',x1+1.5,' ',y1+0.5,',',x1+0.5,' ',y1+0.5,'))'))
    -> FROM (SELECT 0 x1 UNION ALL
    ->    SELECT 1 UNION ALL
    ->    SELECT 2 UNION ALL
    ->    SELECT 3 UNION ALL
    ->    SELECT 4 UNION ALL
    ->    SELECT 5 UNION ALL
    ->    SELECT 6 UNION ALL
    ->    SELECT 7 UNION ALL
    ->    SELECT 8
    ->    ) t1,
    ->   (SELECT 0 y1 UNION ALL
    ->    SELECT 1 UNION ALL
    ->    SELECT 2 UNION ALL
    ->    SELECT 3 UNION ALL
    ->    SELECT 4 UNION ALL
    ->    SELECT 5 UNION ALL
    ->    SELECT 6 UNION ALL
    ->    SELECT 7 UNION ALL
    ->    SELECT 8
    ->    ) t2
    -> ;
Query OK, 81 rows affected (0.01 sec)
Records: 81  Duplicates: 0  Warnings: 0

mysql>
mysql> -- Ну и получение всех мест, где помещается 2х2. Ежели много, есть LIMIT
mysql> SELECT x.x1,x.y1
    -> FROM p,x
    -> GROUP BY x.x1,x.y1
    -> HAVING SUM(MBRIntersects(p,rng)) = 0
    -> ;
+------+------+
| x1   | y1   |
+------+------+
|    2 |    4 |
|    2 |    7 |
|    2 |    8 |
|    3 |    4 |
|    3 |    7 |
|    3 |    8 |
|    4 |    4 |
|    4 |    7 |
|    4 |    8 |
|    5 |    4 |
|    5 |    7 |
|    5 |    8 |
|    6 |    0 |
|    6 |    1 |
|    6 |    7 |
|    6 |    8 |
|    7 |    7 |
|    7 |    8 |
|    8 |    7 |
|    8 |    8 |
+------+------+
20 rows in set (0.00 sec)
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317136
bochkov,

118 сек, и составной по 4 полям и два составных x1 x1 и y1 y2....

жаль-жаль, думал что все-таки выход есть - ан нет))

а без ORDER BY сервер выдает первое попавшее, но согласно индекса?
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317240
Akina,

общий размер сетки 2000х2000, где это видно в вашем примере? у вас для варианта сетки 10х10. на 10х10 можно и вручную искать, сидя рядом с сервером и получая от него запрос...))
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317243
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Олег Котейкин , ты способен понять разницу между идеей и реализацией?
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317281
Akina,
наверное нет, простите старикашку. понять-простить.
...
Рейтинг: 0 / 0
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
    #39317351
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
таблица 2000x2000 эта таблица на 4млн записей
например назовём её grid
колонки x1 y1 x2 y2
таблица содержит описание клеток
в неё никто никогда не пишет
только читают с блокировкой
перед тем как вставить данные в m_items
и таким образом
1 получаем возможную для закраски область
2 читаем клетки for update из grid, в соответствии с этой областью
3 обновляем m_items
...
Рейтинг: 0 / 0
25 сообщений из 35, страница 1 из 2
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]