Create table (transact-sql) identity (property)
Содержание:
Первичный ключ SQL при создании таблицы
Следующий SQL создает первичный ключ в столбце «ID» при создании таблицы «персоны»:
MySQL:
CREATE TABLE Persons
(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
Чтобы разрешить именование ограничения первичного ключа, а также для определения ограничения первичного ключа для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Примечание: В приведенном выше примере имеется только один первичный ключ (пк_персон).
Однако значение первичного ключа состоит из двух столбцов (идентификатор + фамилия).
Syntax for MySQL
The following SQL statement defines the «Personid» column to be an auto-increment primary key field in the «Persons» table:
CREATE TABLE Persons
(
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
ALTER TABLE Persons AUTO_INCREMENT=100;
To insert a new record into the «Persons» table, we will NOT have to specify a value for the «Personid»
column (a unique value will be added automatically):
INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’);
The SQL statement above would insert a new record into the «Persons» table. The
«Personid» column would be assigned a unique value. The «FirstName» column would be set to
«Lars» and the «LastName» column would be set to «Monsen».
Изменение/сброс счётчика AUTO_INCREMENT без очистки таблицы
Другим вариантом является следующий SQL запрос:
ALTER TABLE имяВашейТаблицы AUTO_INCREMENT = 1
Обратите внимание, что AUTO_INCREMENT можно установить на любое значение — но помните об ограничении для непустых таблиц InnoDB.
И ещё очень важное замечание об ALTER TABLE. Дело в том, что этот запрос приводит к перестроению всей таблицы
То есть MySQL создаст новую таблицу с той же структурой и добавит новое значение auto_increment и скопирует все записи с оригинальной таблицы, удалит оригинальную таблицу и переименует новую. Если таблица содержит много записей, то этот процесс может затянуться очень надолго.
Если нужно увеличить значение счётчика AUTO_INCREMENT, то проще вставить ненужную строку (а затем удалить, если нужно). Это займёт долю секунды, в то время как ALTER TABLE может потребовать дни для больших таблиц.
Допустим, я у меня есть таблица со столбцом auto_increment ID и другими столбцами col1, col2…:
INSERT INTO имяВашейТаблицы SET ID = 10000000; DELETE FROM имяВашейТаблицы WHERE ID = 10000000;
Как уменьшить значение AUTO_INCREMENT для InnoDB
Предположим, в таблице имеется 10 записей, при этом у последней записи столбец AUTO_INCREMENT равен 20. Как изменить значение этого столбца ID, чтобы следующая запись имела номер 11?
Это невозможно сделать ни одним из описанных выше методов, но есть другие техники, которые могут иметь сторонние эффекты — поэтому будьте весьма осторожны с ними.
Итак, первый вариант — убрать свойство AUTO_INCREMENT у столбца ID, выполнить запрос для переназначения ID, и затем вернуть AUTO_INCREMENT:
ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED; COMMIT; ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED AUTO_INCREMENT; COMMIT;
Не нужно знать текущее максимальное значение. Этим способом счётчик автоматического прибавления будет сброшен и запущен автоматически с максимального существующего значения.
Но помните о медленной работе ALTER TABLE для больших таблиц.
Ещё один вариант — полностью удалить столбец id, а затем его вернуть с предыдущими настройками. Все поля внутри таблицы заполняются новыми значениями счётчика.
ALTER TABLE news_feed DROP id ALTER TABLE news_feed ADD id BIGINT( 200 ) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (id)
Помните о том, что:
- все поля внутри таблицы будут посчитаны заново и у них будут другие id
- описанный метод полностью поломает foreign keys
Основывается на:
Примеры сбрасывания значения автоматического приращения в MySQL
Сначала создайте таблицу с именем tmp и присвойте атрибут AUTO_INCREMENT столбцу id первичного ключа.
CREATE TABLE tmp ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(45) DEFAULT NULL, PRIMARY KEY (id) );
Во-вторых, вставьте пример данных в таблицу tmp:
INSERT INTO tmp(name)
VALUES('test 1'),
('test 2'),
('test 3');
В- третьих, запрос к таблице tmp для проверки операции вставки:
SELECT * FROM tmp;
У нас есть три строки со значениями столбца ID: 1, 2 и 3. Отлично! Пора попрактиковаться в сбросе значения автоинкремента столбца ID.
Использование инструкции ALTER TABLE
Вы можете сбросить значение автоинкремента с помощью оператора ALTER TABLE. Синтаксис оператора ALTER TABLE для сброса значения автоинкремента выглядит следующим образом:
ALTER TABLE table_name AUTO_INCREMENT = value;
Вы указываете имя таблицы после оператора ALTER TABLE и имя value, которое вы хотите сбросить в выражении AUTO_INCREMENT=value.
Обратите внимание, что значение value должно быть больше или равно текущему максимальному значению столбца автоинкремента. Давайте удалим последнюю запись в таблице tmp с id значением 3:
Давайте удалим последнюю запись в таблице tmp с id значением 3:
DELETE FROM tmp WHERE ID = 3;
Если вы вставите новую строку, MySQL назначит 4 столбцу id новой строки. Однако вы можете сбросить число, сгенерированное MySQL, на 3, используя следующую инструкцию ALTER TABLE:
ALTER TABLE tmp AUTO_INCREMENT = 3;
Теперь давайте попробуем вставим новую строку в таблицу tmp и запросить данные из нее, чтобы увидеть эффект:
INSERT INTO tmp(name)
VALUES ('MySQL example 3');
SELECT
*
FROM
tmp;
У нас есть три строки с последним значением автоинкремента, равным 3 вместо 4, что мы и ожидали.
Использование оператора TRUNCATE TABLE
Оператор TRUNCATE TABLE удаляет все данные из таблицы и сбрасывает значение автоинкремента на ноль.
Следующее иллюстрирует синтаксис оператора TRUNCATE TABLE:
TRUNCATE TABLE table_name;
Используя оператор TRUNCATE TABLE, вы удаляете все данные из таблицы навсегда и сбрасываете значение автоинкремента на ноль.
Использование операторов DROP TABLE и CREATE TABLE
Вы можете использовать пару операторов: DROP TABLE и CREATE TABLE, чтобы сбросить столбец автоинкремента. Обратите внимание, что этот метод удаляет все данные из таблицы навсегда. Как и оператор TRUNCATE TABLE, эти операторы удаляют таблицу и воссоздают ее, поэтому значение автоинкремента сбрасывается на ноль
Как и оператор TRUNCATE TABLE, эти операторы удаляют таблицу и воссоздают ее, поэтому значение автоинкремента сбрасывается на ноль.
DROP TABLE table_name; CREATE TABLE table_name(...);
В этой статье вы узнали, как различными способами сбросить значение автоинкремента в MySQL. Первый способ предпочтительнее, потому что он самый простой и не имеет побочных эффектов.
Rdbms
СУБД расшифровывается как система управления реляционными базами данных.
СУБД является основой для SQL, и для всех современных систем баз данных, таких как MS SQL Server, IBM DB2, Oracle, MySQL, и Microsoft Access.
Данные в реляционной СУБД хранятся в объектах базы данных, называемых таблицами. Таблица представляет собой набор связанных записей данных и состоит из столбцов и строк.
Посмотрите на таблицу «Customers»:
Пример
SELECT * FROM Customers;
Каждая таблица разбита на более мелкие сущности, называемые полями. Поля в таблице Customers состоят из идентификатора клиента, CustomerName, ContactName, адреса, города, PostalCode и страны. Поле — это столбец в таблице, предназначенный для поддержки конкретной информации о каждой записи в таблице.
Запись, также называемая строкой, является каждой отдельной записью, которая существует в таблице.
Например, в таблице выше Customers имеется 91 записей. Запись представляет собой горизонтальную сущность в таблице.
Столбец — это вертикальная сущность в таблице, содержащая все сведения, связанные с определенным полем в таблице.
❮ Назад
Дальше ❯
