Меню

Mysql как установить счетчик



Как добавить счетчик (автоинкремент) в запрос

8 ответов

Ох. Уважаемый, сколько же писать о том что нужно дать более полную информацию.

Какую БД Вы используете.

в запрос типа select никак

Вот здесь по transact-SQL

В MySQL есть параметр AUTO_INCREMENT.

если конкретнее то вот

Извините, забыл. FireBird 2.0.
Где-то на этом форуме видел, вроде Охотник333 приводил пример, но найти, к сожалению, не могу =(

Вот два примера. Но прикрутить к себе пока не хватает мозгов.

1) через подзапрос во фразе SELECT:

select (
select count(1)
from t t2
where t1.name >= t2.name
) as rn,
name,
age
from t t1

2) через селф-джойн и группировку:

select count(1) as rn,
t1.name,
t1.age
from t t1
join t t2
on t1.name >= t2.name
group by t1.name, t1.age

если у вас не существует счётчика, то запросом типа select вы его не создадите.
Для этих целей существует запрос ALTER TABLE (о конструкции написано выше)

Функция count() Calculates the number of rows that satisfy a query’s search condition. Available in SQL,DSQL, and isql. Это немного не то что Вы хотите.

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

Мне кажется здесь возникло непонимание нужд автора. Насколько я понял речь не идет об автоинкрементном поле в таблице, а идет о нумерации строк в выборке. Т.е. генераторы тут не при чем.
Вариантов вижу несколько:
1. Использовать хранимую процедуру, и делать выборку из нее.
2. Исользовать запросы, которые вы привели, если их можно модифицировать под ваши нужды. Но ИМХО, самообъединение таблиц для получения номера строки не самый быстродейственный вариант.
3. Нумеровать строки непосредственно в клиентском приложении.

P.S. Не в курсе последних версии FireBird, может там наконец появилась конструкция по аналогии с rownum в Оракле.

mysql> set @var:=0;
Query OK, 0 rows affected (0.00 sec)

Источник

Как изменить счетчик автоматического приращения в MySQL?

У меня есть поле ID, которое является моим первичным ключом и является просто полем int.

У меня меньше!—3—>300 строки, но теперь каждый раз, когда кто-то подписывается, что ID auto inc вводится очень высоко, как 11800089, 11800090, etc. есть ли способ заставить это вернуться, чтобы он мог следовать порядку (310,311,312).

6 ответов

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

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

Если вы используете MySQL или какой-либо другой SQL server, вам нужно:

  1. резервное копирование базы данных
  2. отбросьте столбец id
  3. экспорт данных
  4. TRUNCATE или «пустой» стол
  5. пересоздать

предполагая, что вы используете mysql, потому что вы используете PHP. Вы можете сбросить auto_increment с помощью оператора

будьте осторожны, потому что вещи сломаются, когда значение auto inc перекрывается

счетчик автоматического приращения для таблицы может быть (re)установлен двумя способами:

выполнив запрос, как и другие, уже объясненные:

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

затем просто установите желаемое значение в поле Auto increment как показано на рисунке. Это в основном выполнит запрос, показанный в первом варианте.

Я считаю, что mysql делает выбор max на id и ставит следующий. Попробуйте обновить идентификаторы таблицы до нужной последовательности. Проблема у вас будет, если они связаны, вы должны поставить Каскад на обновление на fk. Вопрос, который приходит мне на ум:

700 что-то меньше, чем 11800090 у вас есть и рядом с 300, где id>0;

Я считаю, что жалобы MySQL, если вы не ставите где

Я играл вокруг аналогичной проблемы и нашел это решение:

Я надеюсь, что это поможет кому-то в подобной ситуации!

Источник

Как сбросить AUTO_INCREMENT в MySQL/MariaDB

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

Читайте также:  Как перепрошить счетчик матрица

Но у AUTO_INCREMENT есть одно интересное свойство — при удалении строк значение счётчика AUTO_INCREMENT не уменьшается. То есть, например, в таблицу было добавлено 10 записей, после этого все записи были удалены, а затем стали добавлять новые записи — в результате у первой новой записи значение столбца с AUTO_INCREMENT будет 11, у второй будет 12 и так далее.

Отсюда иногда возникает задача — сбросить значение AUTO_INCREMENT или установить AUTO_INCREMENT на единицу.

Сброс AUTO_INCREMENT для InnoDB баз данных

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

Как сбросить AUTO_INCREMENT в phpMyAdmin

В веб интерфейсе phpMyAdmin вы можете просмотреть текущее значение AUTO_INCREMENT и изменить его. Для этого выберите таблицу, для которой вы хотите поменять AUTO_INCREMENT, затем перейдите в Операции → Параметры таблицы → AUTO_INCREMENT:

Установите желаемое значение — помните об ограничении для не пустых таблиц InnoDB.

Очистка таблицы и сброс AUTO_INCREMENT

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

Выполнить удаление всех записей вместе со сбросом счётчика AUTO_INCREMENT можно командой:

Изменение/сброс счётчика AUTO_INCREMENT без очистки таблицы

Другим вариантом является следующий SQL запрос:

Обратите внимание, что AUTO_INCREMENT можно установить на любое значение — но помните об ограничении для непустых таблиц InnoDB.

И ещё очень важное замечание об ALTER TABLE. Дело в том, что этот запрос приводит к перестроению всей таблицы. То есть MySQL создаст новую таблицу с той же структурой и добавит новое значение auto_increment и скопирует все записи с оригинальной таблицы, удалит оригинальную таблицу и переименует новую. Если таблица содержит много записей, то этот процесс может затянуться очень надолго.

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

Допустим, я у меня есть таблица со столбцом auto_increment ID и другими столбцами col1, col2…:

Как уменьшить значение AUTO_INCREMENT для InnoDB

Предположим, в таблице имеется 10 записей, при этом у последней записи столбец AUTO_INCREMENT равен 20. Как изменить значение этого столбца ID, чтобы следующая запись имела номер 11?

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

Итак, первый вариант — убрать свойство AUTO_INCREMENT у столбца ID, выполнить запрос для переназначения ID, и затем вернуть AUTO_INCREMENT:

Не нужно знать текущее максимальное значение. Этим способом счётчик автоматического прибавления будет сброшен и запущен автоматически с максимального существующего значения.

Но помните о медленной работе ALTER TABLE для больших таблиц.

Ещё один вариант — полностью удалить столбец id, а затем его вернуть с предыдущими настройками. Все поля внутри таблицы заполняются новыми значениями счётчика.

Помните о том, что:

  • все поля внутри таблицы будут посчитаны заново и у них будут другие id
  • описанный метод полностью поломает foreign keys

Источник

Счётчик

Счетчик в запросе
проблемма такая : нужно получить порядковые номера записей в запросе. то есть чтоб запрос сам.

Автоинкрементный счетчик
В общем такая вот штука: в sql server 2008 не могу понять как добавить запись в которой.

Триггер-счетчик
Есть таблица Участники и отдельная таблица Кол-во участников с int полем amount, которое выступает.

Модифицировать целочисленное поле в счетчик
Вообщем, есть интовое поле и нужно модифицировать его в интовый счетчик create database test .

так в свойствах столбца установлно а вот в если в определении таблицы щулкую на поле то там нет! а там мне как свойства подправить!?

Добавлено через 1 минуту

Листинг 5.3. Синтаксис оператора SET IDENTITY_INSERT
SET IDENTITY_INSERT [[ .] .]
< ON | OFF >;

IDENTITY ( , )
Для подобного автоинкрементного столбца нельзя задавать значение по умолчанию
(предложение DEFAULT). Столбец с такой характеристикой должен иметь тип данных
TINYINT, SMALLINT, INT, BIGINT, DECIMAL или NUMERIC. Если ему задается тип данных
DECIMAL или NUMERIC, то количество дробных знаков должно быть указано нулевым.
Столбец типа IDENTITY может быть в таблице только один.

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

Счетчик в таблице неверно нумерует
Создал таблицу в MS sql server. столбец с ID нумерует без остановки, на пример идет нумерация.

Читайте также:  Формула для расчета оплаты за электроэнергию по двухтарифному счетчику

Установить поле счетчик id (identity) на начальное значение .
Есть поле id decimal(18,0) identity(1,1) NOT NULL. После вставки записей счетчик увеличивается.

Завершить хранимую процедуру выдав ошибку не выполняя ROLLBACK/COMMIT (чтобы счетчик не изменялся)
Прошу помощи в следующем — есть процедура, которая выполняет INSERT. Но перед этим необходимо.

Источник

Mysql как установить счетчик

Я буду время от времени писать интересные технические заметки, мало ли разработчики тоже читают наш блог. 🙂 Итак, сегодня попытаемся решить очень простую задачку – организовать счетчики в MySQL. Для чего это нужно? Примеров использования очень много: нужно для каждой страницы сайта хранить количество обращений, при обработке большого объема данных нужно посчитать частоты встречаемости элементов, нужно контролировать количество запросов для каждого пользователя в системе… В общем, задача частенько встречается.

Используем “ненадежные” хранилища

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

  • Храните все счетчики в памяти и периодически сбрасывайте контрольные значения на диск в инкрементальные файлы с указанием временной метки. Такой способ позволяет потерять только часть данных от последнего сохранения.
  • Используйте Redis с его возможностью ведения счетчиков. Работает очень быстро и достаточно надежно. Но, если у вас еще не используется Redis, то добавлять его только для счетчиков достаточно спорно. И вторая проблема заключается в транзакционности – счетчик нужно обновлять при успешном прохождении транзакции (в идеале в рамках транзакции), а иначе иногда при падениях или остановках системы счетчик может рассинхронизироваться с реалиями.
  • Используйте ZooKeeper с его распределенными счетчиками. Он предлагает функциональности больше чем вам нужно, но в некоторых случаях работает очень даже неплохо. Снова таки, вводить ZooKeeper только для счетчиков я не рекомендую. И при большой нагрузке он может стать узким местом, так как работает в один поток.

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

Тупое решение в лоб

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

Ну и чтобы обновить счетчик, нужно вызвать банальный код:

У этого решения есть несколько “сюрпризов”. Первый из них очень простой и понятен каждому, кто знает как работают базы данных. В случае обновления счетчика из нескольких потоков все они будут заблокированы и выполнятся в порядке очереди. Поэтому производительность будет не совсем радостной. Но есть и еще один интересный “сюрприз” – наличие deadlock-ов в банальном коде. Они могут проявляться сразу в нескольких случаях:

  • Вы в одной транзакции обновляете сразу несколько счетчиков. Параллельно подобных транзакций может выполняться несколько. Если вы не контролируете порядок resource_id, то в силу специфики захвата lock-ов по индексу (в примере он PK, но в любом случае он понятное дело у вас должен быть, чтобы поиск счетчика по resource_id работал быстро) вы получите deadlock.
  • Еще один сценарий deadlock-а появится, если вы будете производить действия с самой таблицей ресурса в той же транзакции для другой записи (например, дочерней или родительской) а вместо простого индекса будете использовать FK на таблицу ресурса.

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

Только вставки, ничего кроме вставок

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

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

Подсчитываем промежуточные итоги

Чтобы избавиться от “мусорных” исторических данных существует множество однотипных простых техник. Нужно время от времени агрегировать данные и удалять или “виртуально удалять” старые. Давайте рассмотрим немного детальнее. Вы запускаете job в отдельном потоке либо на уровне БД либо на уровне вашего приложения. Этот job бежит по таблице счетчиков, подсчитывает сумму по каждому ресурсу и вместо набора записей оставляет ровно одну со значением суммы. Как это реализовать технически:

  • Блокировать доступ к определенному ресурсу на время проведения операции. Проблема заключается в том, что другие потоки продолжают вставлять значения. А это значит, что без блокировки вы удалите старые записи, среди которых могут быть и новые. Тем самым рискуете поломать счетчик. Блокировку можно делать как на уровне БД так и в коде. Способ не очень хороший, потому что блокировки всегда замедляют работу.
  • Второй способ заключается в добавлении новой колонки с временем добавления записи (тип TIMESTAMP). Теперь вы можете безопасно посчитать сумму за 5 минут в прошлое и удалить записи, которые уже не нужны в той же транзакции. Сумма добавляется как новая запись за текущее время. На самом деле такой способ тоже чреват дополнительными блокировками при удалении и deadlock-ами при параллельной вставке и удалении по индексу, который у вас есть на resource_id.
  • Третий способ заключается в том, чтобы использовать отдельный поток для чистки, а агрегированные записи помечать специальным маркером. В этом случае правильное значение счетчика будет равно сумме всех записей, начиная с последнего агрегированного значения. Исторические данных могут удаляться по одной или блоками в любое время безо всякого риска.
Читайте также:  Счетчик без пломбы последствия

Третий способ, пожалуй, является самым быстрым и безопасным из перечисленных в плане блокировок. Подобное решение является “виртуальным удалением” и часто используется для подсчета баланса за определенные периоды. Агрегированные записи могут как удаляться так и оставаться в системе для построения временных графиков. Подсчет значения счетчика более-менее фиксирован по времени и зависит от периода агрегации.

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

Чистим за собой быстро

Чтобы избежать удаления данных, нужно немного напрячься и вспомнить об операции TRUNCATE TABLE. Она очищает данные очень быстро. Но не все так просто, придется немного изменить алгоритм обновления значений счетчиков.

Для этого нам понадобятся вместо одной сразу 2 или 3 таблички одинаковой структуры: resource_counter, resource_counter_shadow, resource_counter_total (эта табличка является необязательной). Каждая из них будет поддерживать только вставки. Ваше приложение пишет все изменения значения счетчика в виде дополнительных записей в таблицу resource_counter и только в нее. Параллельно работает отдельный поток, который раз в определенное время производит замену таблиц:

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

Работать подобный запрос будет достаточно быстро, потому что таблица resource_counter_shadow маленькая и контролируется интервалом агрегации. Можно также использовать необязательную таблицу resource_counter_total, которая заведена для оптимизации (чтобы избежать переливания данных из одной таблички в другую, если они не меняются). Сделать это можно следующим запросом:

Есть еще более симпатичная версия этого же запроса:

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

Применяем мульти-счетчик

В какой-то момент времени вы задумаетесь, а не слишком ли все стало сложно и обратитесь к изначальной проблеме. Она заключалась в том, что запись в таблице счетчика блокируется при обновлении. Но в то же время, формирование отдельных записей для каждого обновления приводит к росту их количества и необходимости чистить данные. Тогда надо использовать золотую середину. Для этого в таблицу счетчика добавляется новая колонка counter_index, которая включается в состав PK:

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

Для получения значения счетчика по определенному ресурсу по-прежнему нужно будет использовать сумму, но по фиксированному количеству строк (максимум 10 в нашем примере):

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

Как видите, решений такой простенькой задачи достаточно много, если знать предметную область и тщательно тестировать ваши решения. Надеюсь, это сэкономит кому-то время. 🙂

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

Не хочешь пропускать ничего интересного? Подпишись на ленту RSS или следи за нами в Twitter!

Источник

Adblock
detector