ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Вопрос по on duplicate в таблице с auto_increment
12 сообщений из 12, страница 1 из 1
Вопрос по on duplicate в таблице с auto_increment
    #39468596
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Есть БД, например с такой таблицей:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE TABLE `tbl_base_billing` (
	`CODE` INT(11) NOT NULL AUTO_INCREMENT,
	`USERCODE` INT(11) NULL DEFAULT NULL,
	`BILLCODE` INT(11) NULL DEFAULT NULL,
	`WORKSTATUS` INT(3) NULL DEFAULT NULL,
	`DATEPAID` DATE NULL DEFAULT NULL,
	`GROUPN` VARCHAR(38) NULL DEFAULT NULL,
	`BALANS` DOUBLE NULL DEFAULT NULL,
	PRIMARY KEY (`CODE`),
	UNIQUE INDEX `FULLCODE` (`BILLCODE`, `USERCODE`),
	INDEX `USERCODE` (`USERCODE`),
	INDEX `BILLCODE` (`BILLCODE`),
	INDEX `WORKSTATUS` (`WORKSTATUS`),
	INDEX `GROUPN` (`GROUPN`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;



Есть скрипт, который синхронизирует эту БД с другой БД, используя SQL-запросы примерно такого вида:
Код: sql
1.
2.
insert into tbl_base_billing (`BILLCODE`,`USERCODE`,`GROUPN`,`WORKSTATUS`,`DATEPAID`) values (?,?,?,?,?)
on duplicate key update `GROUPN`=?,`WORKSTATUS`=?,`DATEPAID`=?;



Скрипт довольно долго работал нормально, а сегодня вдруг сломался.
Как оказалось, счетчик auto_increment превысил 2 миллиарда и сгенерированное значение нельзя вставить в поле CODE из-за переполнения.
В качестве временного решения я для поля CODE поменял тип данных на bigint, сейчас скрипт снова работает нормально.
Есть еще несколько подобных мест, но там счетчик пока до 2 миллиардов не дошел и вмешательство не требуется.

Я как бы озадачен.
Скрипт синхронизации запускается каждые 5 минут, объем вставляемых данных при каждом запуске порядка 5-8к записей, однако 99% этих данных уже существуют в конечной БД и должны не вставляться, а обновляться.
Однако исходя из значения счетчика, похоже что счетчик увеличивается при каждой попытке insert, даже если срабатывает on duplicate key.
Выглядит бредово, но других объяснений я пока не вижу.
Это возможно? Или все же нужно более подробно изучать скрипт синхронизации и искать ошибки в нем?
...
Рейтинг: 0 / 0
08.06.2017, 13:39
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468606
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Alibek B.,

сначала увеличивается автоинкремент, только потом проверяются уникальные индексы и соответственно там срабатывает on duplicate.

Может у вас BILLCODE & USERCODE должен быть первичным ключом вообще?
...
Рейтинг: 0 / 0
08.06.2017, 13:48
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468614
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Фактически это уникальный ключ (индекс FULLCODE добавил уже я), однако сделать эти поля первичным ключом во всей информационной системе я не смогу, ее нужно будет переписывать.

Понятно, значит придется переделывать скрипт синхронизации, чтобы он делал отдельно update и отдельно insert.
...
Рейтинг: 0 / 0
08.06.2017, 13:52
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468645
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Alibek B. , я бы предложил заменить INSERT .. ODKU на REPLACE. И текст будет короче, и параметры дублировать не нужно.
...
Рейтинг: 0 / 0
08.06.2017, 14:14
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468656
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Оператор REPLACE работает точно так же, как INSERT, за исключением того, что если старая запись в данной таблице имеет то же значение индекса UNIQUE или PRIMARY KEY, что и новая, то старая запись перед занесением новой будет удалена.
Насколько я понимаю, проблему с быстрым ростом счетчика это все равно не решит.
Так что мне в любом случае придется переделывать скрипт.
...
Рейтинг: 0 / 0
08.06.2017, 14:23
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468664
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Alibek B.Насколько я понимаю, проблему с быстрым ростом счетчика это все равно не решит.
Да. Но те не менее сравни:
Код: sql
1.
2.
insert into tbl_base_billing (`BILLCODE`,`USERCODE`,`GROUPN`,`WORKSTATUS`,`DATEPAID`) values (?,?,?,?,?)
on duplicate key update `GROUPN`=?,`WORKSTATUS`=?,`DATEPAID`=?;


Код: sql
1.
replace into tbl_base_billing (`BILLCODE`,`USERCODE`,`GROUPN`,`WORKSTATUS`,`DATEPAID`) values (?,?,?,?,?);
...
Рейтинг: 0 / 0
08.06.2017, 14:27
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468677
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Согласен, что replace лучше, чем insert ... on duplicate key.
Но получается так, что в таблице со счетчиком их использовать нельзя.
Я их использовал, чтобы на клиенте (скрипте) не заморачиваться с проверкой, существует ли уже такая запись. Но выходит, что придется заморочиться.
...
Рейтинг: 0 / 0
08.06.2017, 14:34
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468682
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Akina,

Разве у replace не поплывёт сам счётчик? У строки же будет новый автоинкремент на каждом обновлении.
Ну а если это ок - то выкинуть к чертям автоинкремент. Упаковать вместо него в bigint USERCODE со смещением в 4 байта плюс BILLCODE, раз уж приложению это поле нужно
...
Рейтинг: 0 / 0
08.06.2017, 14:37
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468686
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
MelkijРазве у replace не поплывёт сам счётчик? У строки же будет новый автоинкремент на каждом обновлении.А у него этот счётчик - декоративный. И зачем существует, вообще непонятно.
...
Рейтинг: 0 / 0
08.06.2017, 14:41
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468794
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Приложение, которое использует эту БД, написано не мной. Оно использует поле CODE, так что выкинуть поле нельзя.
А если убрать счетчик - нужно будет озаботиться тем, чтобы генерировать уникальный CODE при вставке новой записи.
...
Рейтинг: 0 / 0
08.06.2017, 15:51
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468806
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Alibek B.,

так у вас же уже есть 64-битный уникальный идентификатор. Зачем ещё что-то изобретать? Разобраться только с null'ами. Скорей всего их там быть всё равно не может, но по ddl сейчас допускаются.
Если версия базы позволяет - то можно generated column сделать вместо физического поля.
...
Рейтинг: 0 / 0
08.06.2017, 15:57
| Ответить | Цитировать | Написать  
Вопрос по on duplicate в таблице с auto_increment
    #39468867
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Alibek B.Оно использует поле CODE, так что выкинуть поле нельзя.Тогда Replace отпадает.
...
Рейтинг: 0 / 0
08.06.2017, 16:37
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Вопрос по on duplicate в таблице с auto_increment
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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