Как изменить столбец mysql

Изменение таблиц и столбцов

Последнее обновление: 11.05.2018

Если таблица уже была ранее создана, и ее необходимо изменить, то для этого применяется команда ALTER TABLE. Ее
сокращенный формальный синтаксис:

ALTER TABLE название_таблицы 
{ ADD название_столбца тип_данных_столбца [атрибуты_столбца] | 
  DROP COLUMN название_столбца |
  MODIFY COLUMN название_столбца тип_данных_столбца [атрибуты_столбца] |
  ALTER COLUMN название_столбца SET DEFAULT значение_по_умолчанию |
  ADD [CONSTRAINT] определение_ограничения |
  DROP [CONSTRAINT] имя_ограничения}

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

Добавление нового столбца

Добавим в таблицу Customers новый столбец Address:

ALTER TABLE Customers
ADD Address VARCHAR(50) NULL;

В данном случае столбец Address имеет тип VARCHAR и для него определен атрибут NULL.

Удаление столбца

Удалим столбец Address из таблицы Customers:

ALTER TABLE Customers
DROP COLUMN Address;

Изменение значения по умолчанию

Установим в таблице Customers для столбца Age значение по умолчанию 22:

ALTER TABLE Customers
ALTER COLUMN Age SET DEFAULT 22;

Изменение типа столбца

Изменим в таблице Customers тип данных у столбца FirstName на CHAR(100) и установим для него атрибут NULL:

ALTER TABLE Customers
MODIFY COLUMN FirstName CHAR(100) NULL;

Добавление и удаление внешнего ключа

Пусть изначально в базе данных будут добавлены две таблицы, никак не связанные:

CREATE TABLE Customers
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Age INT, 
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL
);
CREATE TABLE Orders
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT,
    CreatedAt Date
);

Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:

ALTER TABLE Orders
ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

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

ALTER TABLE Orders
ADD CONSTRAINT orders_customers_fk 
FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

В данном случае ограничение внешнего ключа называется orders_customers_fk. Затем по этому имени мы можем удалить ограничение:

ALTER TABLE Orders
DROP FOREIGN KEY orders_customers_fk;

Добавление и удаление первичного ключа

Добавим в таблицу Products первичный ключ:

CREATE TABLE Products
(
    Id INT,
    Model VARCHAR(20)
);

ALTER TABLE Products
ADD PRIMARY KEY (Id);

Теперь удалим первичный ключ:

ALTER TABLE Products
DROP PRIMARY KEY;

В этом учебном пособии вы узнаете, как использовать MySQL оператор ALTER TABLE для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы (с синтаксисом и примерами).

Описание

MySQL оператор ALTER TABLE используется для добавления, изменения или удаления столбцов в таблице. Оператор MySQL ALTER TABLE также используется для переименования таблицы.

Добавить столбец в таблицу

Синтаксис

Синтаксис добавления столбца в таблицу MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ];

table_name — имя таблицы для изменения.
new_column_name — имя нового столбца для добавления в таблицу.
column_definition — тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, то новый столбец будет добавлен в конец таблицы.

Пример

Рассмотрим пример, который показывает, как добавить столбец в таблицу MySQL, используя оператор ALTER TABLE.
Например:

Этот MySQL пример ALTER TABLE добавит столбец с именем last_name в таблицу contacts. Он будет создан как столбец NOT NULL и появится в таблице после поля contact_id.

Добавить несколько столбцов в таблицу

Синтаксис

Синтаксис добавления нескольких столбцов в таблицу MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
…
;

table_name — имя таблицы для изменения.
new_column_name — имя нового столбца для добавления в таблицу.
column_definition — тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, новый столбец будет добавлен в конец таблицы.

Пример

Рассмотрим пример, который показывает, как добавить несколько столбцов в таблицу MySQL, используя оператор ALTER TABLE.
Например:

Этот пример ALTER TABLE добавит в таблицу contacts два столбца — last_name и first_name.

Поле last_name будет создано как столбец varchar (40) NOT NULL и появится в таблице contacts после столбца contact_id. Столбец first_name будет создан как столбец NULL varchar (35) и появится в таблице после столбца last_name.

Изменить столбец в таблице

Синтаксис

Синтаксис для изменения столбца в таблице MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name ];

table_name — имя таблицы для изменения.
column_name — имя столбца для изменения в таблице.
column_definition — измененный тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице помещается столбец, если вы хотите изменить его позицию.

Пример

Рассмотрим пример, который показывает, как изменить столбец в таблице MySQL с помощью оператора ALTER TABLE.
Например:

Этот пример ALTER TABLE изменит столбец с именем last_name как тип данных varchar (50) и установит для столбца значения NULL.

Изменить несколько столбцов в таблице

Синтаксис

Синтаксис для изменения нескольких столбцов в таблице MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
…
;

table_name — имя таблицы для изменения.
column_name — имя столбца для изменения в таблице.
column_definition — измененный тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице помещается столбец, если вы хотите изменить его позицию.

Пример

Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице MySQL, используя оператор ALTER TABLE.

Этот пример ALTER TABLE будет изменять в таблице contacts два столбца — last_name и first_name.
Поле last_name будет изменено на столбец NULL varchar (55) и появится в таблице после столбца contact_type. Столбец first_name будет изменен на столбец varchar (30) NOT NULL (и не изменит позицию в определении таблицы contacts, так как не указано FIRST | AFTER).

Удаление столбца из таблицы

Синтаксис

Синтаксис для удаления столбца из таблицы в MySQL (с использованием оператора ALTER TABLE):
Например:

ALTER TABLE table_name
DROP COLUMN column_name;

table_name — имя таблицы для изменения.
column_name — имя столбца для удаления из таблицы.

Пример

Рассмотрим пример, который показывает, как удалить столбец из таблицы в MySQL с помощью оператора ALTER TABLE.
Например:

Этот пример ALTER TABLE удаляет столбец с именем contact_type из таблицы contacts.

Переименование столбца в таблице

Синтаксис

Синтаксис для переименования столбца в таблице MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
CHANGE COLUMN old_name new_name
column_definition
[ FIRST | AFTER column_name ];

table_name — имя таблицы для изменения.
old_name — столбец для переименования.
new_name — новое имя столбца.
column_definition — тип данных и определение столбца (NULL или NOT NULL и т. д.). Вы должны указать определение столбца при переименовании столбца, даже если оно не изменится.
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице помещается столбец, если вы хотите изменить его позицию.

Пример

Рассмотрим пример, который показывает, как переименовать столбец в таблице MySQL с помощью оператора ALTER TABLE.
Например:

Этот MySQL пример ALTER TABLE переименует столбец с именем contact_type в ctype. Столбец будет определен как столбец varchar (20) NOT NULL.

Переименовать таблицу

Синтаксис

Синтаксис для переименования таблицы в MySQL:

ALTER TABLE table_name
RENAME TO new_table_name;

table_name — таблица для переименования.
new_table_name — новое имя таблицы для использования.

Пример

Рассмотрим пример, который показывает, как переименовать таблицу в MySQL с помощью оператора ALTER TABLE.
Например:

Этот пример ALTER TABLE переименует таблицу contacts в people.

Узнайте как использовать оператор ALTER TABLE в распространенных БД:

• ALTER TABLE Oracle PL/SQL
• ALTER TABLE SQL Server
• ALTER TABLE PostgreSQL
• ALTER TABLE MariaDB
• ALTER TABLE SQLite

SQL-запрос для изменения типа столбца в базе данных SQL Server

Мы можем использовать оператор ALTER TABLE ALTER COLUMN для изменения типа столбца в таблице. Использует следующий синтаксис:

ALTER TABLE [tbl_name] ALTER COLUMN [col_name] [DATA_TYPE]

Здесь

• tbl_name: задает имя таблицы.
• col_name: задает имя столбца, тип которого мы хотим изменить. col_name должно быть указано после ключевых слов ALTER COLUMN.
• DATA_TYPE: задает новый тип данных и длину столбца.

В целях демонстрации я создал таблицу с именем tblStudent.

CREATE TABLE [dbo].[tblstudent]

    (

       [id]                [INT] IDENTITY(1, 1) NOT NULL,

       [student_code]      [VARCHAR](20) NOT NULL,

       [student_firstname] [VARCHAR](250) NOT NULL,

       [student_lastname]  [VARCHAR](10) NOT NULL,

       [address]           [VARCHAR](max) NULL,

       [city_code]         [VARCHAR](20) NOT NULL,

       [school_code]       [VARCHAR](20) NULL,

       [admissiondate]     [DATETIME] NULL,

       CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ( [id] ASC )

    )

Предположим, что вы хотите изменить тип данных [address] с varchar(max) на nvarchar(1500). Выполните следующий запрос для изменения типа столбца.

Alter table tblstudent alter column address nvarchar(1500)

Проверим изменения с помощью следующего скрипта.

use StudentDB

go

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS 

where table_name='tblStudent'

Видно, что тип данных столбца изменился.

Важные замечания:

1. При уменьшении размера столбца SQL Server проверит данные в таблице и, если данные превышают новую длину, вернет предупреждение и прервет выполнение оператора.
2. При изменении типа данных nvarchar на varchar, если столбец содержит строку Юникод, то SQL Server возвращает ошибку и прерывает оператор.
3. В отличие от MySQL изменение типа данных нескольких столбцов не допускается.
4. Вы не можете добавить
   а. ограничение NOT NULL, если столбец содержит NULL-значения;
   б. ограничение UNIQUE, если в столбце имеются дубликаты.

Запрос SQL для изменения типа столбца в MySQL

Для изменения типа данных столбца мы можем использовать оператор ALTER TABLE MODIFY COLUMN. Синтаксис изменения типа данных столбца имеет следующий вид:

ALTER TABLE [tbl_name] MODIFY COLUMN [col_name_1] [DATA_TYPE], 

    MODIFY [col_name_2] [data_type], 

    MODIFY [col_name_3] [data_type]

Здесь

• Tbl_name: задает имя таблицы, содержащая столбец, который мы хотим изменить.
• Col_name: задает имя столбца, тип которого мы хотим изменить. Col_name должно быть указано после ключевых слов MODIFY COLUMN. Мы можем изменить тип данных нескольких столбцов. При изменении типа данных нескольких столбцов, столбцы разделяются запятой (,).
• Datatype: задает новый тип данных и длину столбца. Тип данных должен указываться после имени столбца.

В целях демонстрации я создал таблицу с именем tblactor в базе данных DemoDatabase. Вот код, который создает таблицу.

create table tblactor

    (

    actor_id int,

    first_name varchar(500),

    first_name varchar(500),

    address varchar(500),

    CityID int,

    lastupdate datetime

    )

Рассмотрим несколько примеров.

Пример 1: Запрос для изменения типа данных одного столбца

Мы хотим изменить тип столбца address с varchar(500) на тип данных TEXT. Выполните следующий запрос для изменения типа данных.

mysql> ALTER TABLE tblActor MODIFY address TEXT
Для проверки изменений выполните следующий запрос:

mysql> describe tblactor

Как можно увидеть, тип данных столбца address был изменен на TEXT.

Пример 2: SQL-запрос для изменения типа данных нескольких столбцов

Мы можем изменить тип данных нескольких столбцов в таблице. В нашем примере мы хотим изменить тип столбцов first_name и last_name. Новым типом данных столбцов становится TINYTEXT.

mysql> ALTER TABLE tblActor MODIFY first_name TINYTEXT, modify last_name TINYTEXT;
Выполните следующий запрос, чтобы проверить изменения:

mysql> describe tblActor

Как видно, тип данных столбцов first_name и last_name изменился на TINYTEXT.

Пример 3: Переименование столбца в MySQL

Чтобы переименовать столбцы, мы должны использовать оператор ALTER TABLE CHANGE COLUMN. Предположим, что вы хотите переименовать столбец CityID в CityCode; вы должны выполнить следующий запрос.

mysql> ALTER TABLE tblActor CHANGE COLUMN CityID CityCode int
Выполните команду describe, чтобы увидеть изменения структуры таблицы.

Видно, что имя столбца изменилось.

Запрос SQL для изменения типа столбца в базе данных PostgreSQL

Мы можем использовать оператор ALTER TABLE ALTER COLUMN для изменения типа данных столбца. Синтаксис изменения типа данных столбца:

ALTER TABLE [tbl_name] ALTER COLUMN [col_name_1] TYPE [data_type], 

ALTER COLUMN [col_name_2] TYPE [data_type], 

ALTER COLUMN [col_name_3] TYPE [data_type]

Здесь

• Tbl_name: задает имя таблицы, содержащая столбец, который вы хотите изменить.
• Col_name: задает имя столбца, тип которого мы хотим изменить. Col_name должно быть указано после ключевых слов ALTER COLUMN. Мы можем изменить тип данных нескольких столбцов.
• Data_type: задает новый тип данных и длину столбца. Тип данных должен быть указан после ключевого слова TYPE.

В целях демонстрации я создал таблицу с именем tblmovies в базе данных DemoDatabase. Вот код для создания этой таблицы:

create table tblmovies

    (

    movie_id int,

    Movie_Title varchar(500),

    Movie_director TEXT,

    Movie_Producer TEXT,

    duraion int,

    Certificate varchar(5),	

    rent numeric(10,2)

    )

Теперь рассмотрим несколько примеров.

Пример 1: Запрос SQL для изменения типа данных одного столбца

Мы хотим изменить тип столбца movie_id с типа данных int4 на int8. Для изменения типа данных выполните следующий запрос.

ALTER TABLE tblmovies ALTER COLUMN movie_id TYPE BIGINT

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

SELECT 

    table_catalog,

    table_name, 

    column_name, 

    udt_name,

    character_maximum_length 

 FROM 

    information_schema.columns

 WHERE 

    table_name = 'tblmovies';

Как видно, тип данных столбца movie_id стал int8.

Пример 2: Запрос SQL для изменения типа данных нескольких столбцов

Мы можем изменить тип данных сразу нескольких столбцов таблицы. В нашем примере мы хотим изменить тип столбцов movie_title и movie_producer. Новым типом данных для столбца movie_title становится TEXT, а для movie_producer — varchar(2000).

ALTER TABLE tblmovies ALTER COLUMN movie_title TYPE text, ALTER COLUMN movie_producer TYPE varchar(2000);

Выполните следующий запрос для проверки изменений:

SELECT 

    table_catalog,

    table_name, 

    column_name, 

    udt_name,

    character_maximum_length 

 FROM 

    information_schema.columns

 WHERE 

    table_name = 'tblmovies';

Как видно, типом данных столбца movie_title является TEXT, а movie_producer — varchar(2000).