Изменение столбцов
Тип данных столбца в SQL Server можно изменить с помощью SQL Server Management Studio или Transact-SQL.
Изменение типа данных столбца, в котором уже есть данные, может привести к полной потере данных при преобразовании существующих данных в новый тип. Кроме того, код и приложения, которые используют измененный столбец, могут завершиться сбоем. Это касается запросов, представлений, хранимых процедур, определяемых пользователем функций и клиентских приложений. Следует иметь в виду, что возникновение ошибок происходит каскадом. Например, может произойти сбой хранимой процедуры, которая вызывает определяемую пользователем функцию, зависящую от изменяемого столбца. Внимательно рассмотрите любые изменения, которые необходимо сделать со столбцом таблицы.
Разрешения
Требуется разрешение ALTER на таблицу.
Использование SQL Server Management Studio (SSMS)
Изменение типа данных столбца с помощью SSMS
- В обозревателе объектов щелкните правой кнопкой мыши таблицу со столбцами, масштаб которых необходимо изменить, и выберите Конструктор.
- Выберите столбец, тип данных которого планируется изменить.
- На вкладке Свойства столбца выберите ячейку сетки для свойства Тип данных и выберите новый тип данных из раскрывающегося списка.
- В меню «Файл» выберите «Сохранитьимя таблицы».
При изменении типа данных столбца конструктор таблиц применяет длину типа данных, определенную по умолчанию для выбранного типа данных, даже если была указана другая длина. Всегда устанавливайте необходимое значение длины типа данных после того, как был указан тип данных.
При попытке изменения типа данных столбца, связанного с другими таблицами, конструктор таблиц запрашивает подтверждение на внесение изменений и в столбцы других таблиц.
Использование Transact-SQL
Изменение типа данных столбца с помощью Transact-SQL
- В обозревателе объектов подключитесь к экземпляру ядра СУБД.
- На стандартной панели выберите пункт Создать запрос.
- Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.
CREATE TABLE dbo.doc_exy (column_a INT ); GO INSERT INTO dbo.doc_exy (column_a) VALUES (10); GO ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2); GO Следующие шаги
Манипуляции со столбцами
Набор действий, позволяющих изменять структуру таблицы.
ALTER [TEMPORARY] TABLE [db].name [ON CLUSTER cluster] ADD|DROP|RENAME|CLEAR|COMMENT|MODIFY|ALTER>|MATERIALIZE COLUMN ... В запросе можно указать сразу несколько действий над одной таблицей через запятую. Каждое действие — это манипуляция над столбцом.
Существуют следующие действия:
- ADD COLUMN — добавляет столбец в таблицу;
- DROP COLUMN — удаляет столбец;
- RENAME COLUMN — переименовывает существующий столбец;
- CLEAR COLUMN — сбрасывает все значения в столбце для заданной партиции;
- COMMENT COLUMN — добавляет комментарий к столбцу;
- MODIFY COLUMN — изменяет тип столбца, выражение для значения по умолчанию и TTL;
- MODIFY COLUMN REMOVE — удаляет какое-либо из свойств столбца;
- MATERIALIZE COLUMN — делает столбец материализованным ( MATERIALIZED ) в кусках, в которых отсутствуют значения.
Подробное описание для каждого действия приведено ниже.
ADD COLUMN
ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after | FIRST] Добавляет в таблицу новый столбец с именем name , типом type , кодеком codec и выражением для умолчания default_expr (смотрите раздел Значения по умолчанию).
Если указано IF NOT EXISTS , запрос не будет возвращать ошибку, если столбец уже существует. Если указано AFTER name_after (имя другого столбца), то столбец добавляется (в список столбцов таблицы) после указанного. Если вы хотите добавить столбец в начало таблицы, используйте FIRST . Иначе столбец добавляется в конец таблицы. Для цепочки действий name_after может быть именем столбца, который добавляется в одном из предыдущих действий.
Добавление столбца всего лишь меняет структуру таблицы, и не производит никаких действий с данными — соответствующие данные не появляются на диске после ALTER-а. При чтении из таблицы, если для какого-либо столбца отсутствуют данные, то он заполняется значениями по умолчанию (выполняя выражение по умолчанию, если такое есть, или нулями, пустыми строками). Также, столбец появляется на диске при слиянии кусков данных (см. MergeTree).
Такая схема позволяет добиться мгновенной работы запроса ALTER и отсутствия необходимости увеличивать объём старых данных.
ALTER TABLE alter_test ADD COLUMN Added1 UInt32 FIRST; ALTER TABLE alter_test ADD COLUMN Added2 UInt32 AFTER NestedColumn; ALTER TABLE alter_test ADD COLUMN Added3 UInt32 AFTER ToDrop; DESC alter_test FORMAT TSV; Added1 UInt32 CounterID UInt32 StartDate Date UserID UInt32 VisitID UInt32 NestedColumn.A Array(UInt8) NestedColumn.S Array(String) Added2 UInt32 ToDrop UInt32 Added3 UInt32 DROP COLUMN
DROP COLUMN [IF EXISTS] name Удаляет столбец с именем name . Если указано IF EXISTS , запрос не будет возвращать ошибку, если столбца не существует.
Запрос удаляет данные из файловой системы. Так как это представляет собой удаление целых файлов, запрос выполняется почти мгновенно.
Предупреждение
Вы не можете удалить столбец, используемый в материализованном представлениии. В противном случае будет ошибка.
ALTER TABLE visits DROP COLUMN browser RENAME COLUMN
RENAME COLUMN [IF EXISTS] name to new_name Переименовывает столбец name в new_name . Если указано выражение IF EXISTS , то запрос не будет возвращать ошибку при условии, что столбец name не существует. Поскольку переименование не затрагивает физические данные колонки, запрос выполняется практически мгновенно.
ЗАМЕЧЕНИЕ: Столбцы, являющиеся частью основного ключа или ключа сортировки (заданные с помощью ORDER BY или PRIMARY KEY ), не могут быть переименованы. Попытка переименовать эти слобцы приведет к SQL Error [524] .
ALTER TABLE visits RENAME COLUMN webBrowser TO browser CLEAR COLUMN
CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name Сбрасывает все значения в столбце для заданной партиции. Если указано IF EXISTS , запрос не будет возвращать ошибку, если столбца не существует.
Как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
ALTER TABLE visits CLEAR COLUMN browser IN PARTITION tuple() COMMENT COLUMN
COMMENT COLUMN [IF EXISTS] name 'Text comment' Добавляет комментарий к таблице. Если указано IF EXISTS , запрос не будет возвращать ошибку, если столбца не существует.
Каждый столбец может содержать только один комментарий. При выполнении запроса существующий комментарий заменяется на новый.
Посмотреть комментарии можно в столбце comment_expression из запроса DESCRIBE TABLE.
ALTER TABLE visits COMMENT COLUMN browser 'Столбец показывает, из каких браузеров пользователи заходили на сайт.' MODIFY COLUMN
MODIFY COLUMN [IF EXISTS] name [type] [default_expr] [codec] [TTL] [AFTER name_after | FIRST] ALTER COLUMN [IF EXISTS] name TYPE [type] [default_expr] [codec] [TTL] [AFTER name_after | FIRST] Запрос изменяет следующие свойства столбца name :
- Тип
- Значение по умолчанию
- Кодеки сжатия
- TTL
Примеры изменения кодеков сжатия смотрите в разделе Кодеки сжатия столбцов.
Примеры изменения TTL столбца смотрите в разделе TTL столбца.
Если указано IF EXISTS , запрос не возвращает ошибку при условии, что столбец не существует.
Запрос также может изменять порядок столбцов при помощи FIRST | AFTER , смотрите описание ADD COLUMN.
При изменении типа, значения преобразуются так, как если бы к ним была применена функция toType. Если изменяется только выражение для умолчания, запрос не делает никакой сложной работы и выполняется мгновенно.
ALTER TABLE visits MODIFY COLUMN browser Array(String) Изменение типа столбца — это единственное действие, которое выполняет сложную работу — меняет содержимое файлов с данными. Для больших таблиц, выполнение может занять длительное время.
Выполнение запроса ALTER атомарно.
Запрос ALTER на изменение столбцов реплицируется. Соответствующие инструкции сохраняются в ZooKeeper, и затем каждая реплика их применяет. Все запросы ALTER выполняются в одном и том же порядке. Запрос ждёт выполнения соответствующих действий на всех репликах. Но при этом, запрос на изменение столбцов в реплицируемой таблице можно прервать, и все действия будут осуществлены асинхронно.
MODIFY COLUMN REMOVE
Удаляет какое-либо из свойств столбца: DEFAULT , ALIAS , MATERIALIZED , CODEC , COMMENT , TTL .
ALTER TABLE table_name MODIFY COLUMN column_name REMOVE property; Пример
Удаление свойства TTL:
ALTER TABLE table_with_ttl MODIFY COLUMN column_ttl REMOVE TTL; Смотрите также
MATERIALIZE COLUMN
Материализует или обновляет столбец таблицы с выражением для значения по умолчанию ( DEFAULT или MATERIALIZED ). Используется, если необходимо добавить или обновить столбец со сложным выражением, потому как вычисление такого выражения прямо во время выполнения запроса SELECT оказывается ощутимо затратным.
ALTER TABLE table MATERIALIZE COLUMN col; Пример
DROP TABLE IF EXISTS tmp; SET mutations_sync = 2; CREATE TABLE tmp (x Int64) ENGINE = MergeTree() ORDER BY tuple() PARTITION BY tuple(); INSERT INTO tmp SELECT * FROM system.numbers LIMIT 5; ALTER TABLE tmp ADD COLUMN s String MATERIALIZED toString(x); ALTER TABLE tmp MATERIALIZE COLUMN s; SELECT groupArray(x), groupArray(s) FROM (select x,s from tmp order by x); ┌─groupArray(x)─┬─groupArray(s)─────────┐ │ [0,1,2,3,4] │ ['0','1','2','3','4'] │ └───────────────┴───────────────────────┘ ALTER TABLE tmp MODIFY COLUMN s String MATERIALIZED toString(round(100/x)); INSERT INTO tmp SELECT * FROM system.numbers LIMIT 5,5; SELECT groupArray(x), groupArray(s) FROM tmp; ┌─groupArray(x)─────────┬─groupArray(s)──────────────────────────────────┐ │ [0,1,2,3,4,5,6,7,8,9] │ ['0','1','2','3','4','20','17','14','12','11'] │ └───────────────────────┴────────────────────────────────────────────────┘ ALTER TABLE tmp MATERIALIZE COLUMN s; SELECT groupArray(x), groupArray(s) FROM tmp; ┌─groupArray(x)─────────┬─groupArray(s)─────────────────────────────────────────┐ │ [0,1,2,3,4,5,6,7,8,9] │ ['inf','100','50','33','25','20','17','14','12','11'] │ └───────────────────────┴───────────────────────────────────────────────────────┘ Смотрите также
Ограничения запроса ALTER
Запрос ALTER позволяет создавать и удалять отдельные элементы (столбцы) вложенных структур данных, но не вложенные структуры данных целиком. Для добавления вложенной структуры данных, вы можете добавить столбцы с именем вида name.nested_name и типом Array(T) — вложенная структура данных полностью эквивалентна нескольким столбцам-массивам с именем, имеющим одинаковый префикс до точки.
Отсутствует возможность удалять столбцы, входящие в первичный ключ или ключ для сэмплирования (в общем, входящие в выражение ENGINE ). Изменение типа у столбцов, входящих в первичный ключ возможно только в том случае, если это изменение не приводит к изменению данных (например, разрешено добавление значения в Enum или изменение типа с DateTime на UInt32 ).
Если возможностей запроса ALTER не хватает для нужного изменения таблицы, вы можете создать новую таблицу, скопировать туда данные с помощью запроса INSERT SELECT, затем поменять таблицы местами с помощью запроса RENAME, и удалить старую таблицу. В качестве альтернативы для запроса INSERT SELECT , можно использовать инструмент clickhouse-copier.
Запрос ALTER блокирует все чтения и записи для таблицы. То есть если на момент запроса ALTER выполнялся долгий SELECT , то запрос ALTER сначала дождётся его выполнения. И в это время все новые запросы к той же таблице будут ждать, пока завершится этот ALTER .
Для таблиц, которые не хранят данные самостоятельно (типа Merge и Distributed), ALTER всего лишь меняет структуру таблицы, но не меняет структуру подчинённых таблиц. Для примера, при ALTER-е таблицы типа Distributed , вам также потребуется выполнить запрос ALTER для таблиц на всех удалённых серверах.
Преобразовать тип данных в запросе select
Можно ли не меняя тип данных в таблице, вывести через select столбец с иным типом данных? К примеру, в столбце varchar, а я хочу int (в столбце нет других символов кроме 0-9).
Отслеживать
задан 16 ноя 2022 в 6:24
Андрей Ковров Андрей Ковров
93 7 7 бронзовых знаков
select CAST(СтолбецСтрока AS INT) as НовоеИмяСтолбца from table
16 ноя 2022 в 6:26
Спасибо большое!
16 ноя 2022 в 7:21
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
SELECT CAST(ColumnName AS INT) AS ColumnName FROM TableName Отслеживать
ответ дан 16 ноя 2022 в 6:29
Vitaliy Zlobin Vitaliy Zlobin
1,676 1 1 золотой знак 5 5 серебряных знаков 15 15 бронзовых знаков
TRY_CAST() всё же безопаснее. мало ли что там и кто говорит про значения в поле. Сто пудов у автора нет в таблице констрейнта, который обеспечивает соответствие значения описанному шаблону.
16 ноя 2022 в 6:40
Благодарю, try_cast тоже опробую
Изменение столбца
Определение столбца можно изменить, используя инструкцию ALTER TABLE без предло- жения MODIFY . Изменение столбца может включать изменения типа данных, размера и стандартного значения.
- Можно увеличить ширину или точность числового столбца.
- Можно увеличить ширину символьных столбцов.
- Можно уменьшить ширину столбца в следующих случаях:
- столбец содержит только пустые значения;
- в таблице нет строк;
- уменьшенная ширина столбца не меньше существующих значений в этом столбце;
- Если столбец содержит только пустые значения, можно изменить тип данных. Исключением из этого правила являются преобразования CHAR в VARCHAR2 , которые могут выполняться с данными в столбцах.
- Можно преобразовать столбец CHAR в тип данных VARCHAR2 или преобразовать столбец VARCHAR2 в тип данных CHAR , только если столбец содержит пустые значения или если не изменяется размер.
- Изменение для столбца стандартного значения влияет только на последующие операции вставки в таблицу.
Далее: Порядок использования представлений словаря
Post Views: 1 317
Похожие записи
Предложение WITH
Используя предложение WITH, можно определить блок запроса до его применения в запросе. Предложение WITH (формально называется subquery_factoring_clause) позволяет многократно использовать один и тот же блок запроса в инструкции SELECT, когда она встречается более одного раза в сложном запросе. Это особенно. Читать далее
Оператор WITH в sql
Оператор WITH в SQL — это чрезвычайно полезный инструмент для создания временных таблиц и использования их внутри других запросов. Это позволяет упростить код и улучшить производительность запросов. Оператор WITH (также известный как Common Table Expression) используется для создания временных таблиц. Читать далее
Выполнение запросов внешних таблиц
Внешняя таблица не описывает никаких данных, которые хранятся в базе данных. Внешняя таблица не описывает порядок хранения данных во внешнем источнике. Вместо этого она описывает, как уровень внешней таблицы должен представлять данные для сервера. За преобразования, которые требуется выполнять над. Читать далее
Инструкция FLASHBACK TABLE
Позволяет восстанавливать таблицы до состояния на заданный момент времени с помощью одной инструкции. Восстанавливает табличные данные вместе со связанными индексами и ограничениями. Позволяет возвращать таблицу и ее содержимое в состояние, существовавшее на определенный момент времени, или к изменению системы, определенному. Читать далее
Создание внешней таблицы
Внешние таблицы создаются с помощью предложения ORGANIZATION EXTERNAL инструкции CREATE TABLE. В действительности таблица не создается. Точнее, создаются метаданные в словаре данных, который можно использовать для доступа к внешним данным. Предложение ORGANIZATION применяется для указания порядка, в котором сохраняются строки. Читать далее
Инструкция DROP TABLE … PURGE
В базе данных Oracle имеется функция для удаления таблиц. При удалении таблицы база данных не сразу освобождает пространство, занимаемое таблицей. Точнее, база данных переименовывает таблицу и помещает ее в корзину, где таблица позже может быть восстановлена с помощью инструкции FLASHBACK. Читать далее
Создание внешней таблицы с помощью драйвера доступа ORACLE_LOADER
Рассмотрим, как создаются внешние таблицы посредством драйвера доступа ORACLE_LOADER. Предположим, что существует текстовый файл, в котором имеются записи в следующем формате: 10,jones,11-Dec-1934 20,smith,12-Jun-1972 Записи разделяются символом новой строки, и все поля заканчиваются запятой ( , ). Имя файла: /emp_dir/emp.dat. Читать далее
Пример создание внешней таблицы с помощью драйвера доступа ORACLE_DATAPUMP
Используя драйвер доступа ORACLE_DATAPUMP, можно выполнять с внешними таблицами операции выгрузки и повторной загрузки. Примечание. В контексте внешних таблиц загрузка данных обозначает операцию чтения данных из внешней таблицы и их загрузку в таблицу базы данных. Под выгрузкой данных понимается чтение. Читать далее
Установка Предпочтений SQL Developer
Можно настроить много аспектов интерфейса и среды SQL Developer, изменяя предпочтения SQL Developer согласно Вашим потребностям. Чтобы изменить предпочтения SQL Developer, выберите Tools, а затем Preferences. Настройте интерфейс SQL Developer и среду. В меню Tools выберите Preferences. Предпочтения группируется в. Читать далее
Резюме по SQL Developer
В этой рубрике было рассмотрено использование SQL Developer, чтобы выполнять следующие задачи: Просматривать, создавать и редактировать объекты базы данных Выполнять SQL-операторы и сценарии на Рабочем листе SQL Создавать и сохранять пользовательские отчеты SQL Developer является бесплатным графическим инструментом, позволяющим упростить. Читать далее