В SQL можно выполнять операции взаимодействия между значениями разного типа. Например, присвоение значения определенного типа столбцу таблицы, для которого установлен другой тип данных, либо выполнение некоторых арифметических действий или операций сравнения с участием столбцов с различными типами данных. Такие действия требуют унификации значений, преобразования их к единому типу. SQL в таком случае будет производить неявное преобразование типов. Однако не во всех случаях мы можем полагаться на автоконвертацию. Когда нам необходимо точно указать тип, в который хотим преобразовать значение, мы можем воспользоваться функциями CAST и CONVERT. В этой статье мы сравним неявное и явное преобразование типов и рассмотрим возможности функций CAST SQL и CONVERT SQL.
Обращаем внимание, что примеры с функциями
CASTиCONVERTв этой статье будут представлены с использованием синтаксиса MySQL.
Неявное преобразование типов в SQL
Прежде чем перейти к разбору функций явного преобразования, рассмотрим, когда и по каким принципам SQL производит неявную конвертацию.
Неявное преобразование может происходить при вставке данных в таблицу, когда тип вставляемого значения и тип колонки не соответствуют друг другу. В таком случае SQL попытается неявно преобразовать значение в тип данных столбца перед вставкой.
Допустим, мы создали таблицу таким образом:
CREATE TABLE meetings (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(50) NOT NULL,
start_date_time DATETIME,
end_date_time DATETIME,
attendees_num INT,
price DECIMAL(4,2),
PRIMARY KEY (id)
); Затем мы собираемся добавить новую строку:
INSERT INTO meetings (title, start_date_time, end_date_time, attendees_num, price)
VALUES ('IT conference', '2023-03-23 09:00:00', '2023-03-23 10:00:00', '120', '80.5'); | id | title | start_date_time | end_date_time | attendees_num | price |
|---|---|---|---|---|---|
| 1 | IT conference | 2023-03-23 09:00:00 | 2023-03-23 10:00:00 | 120 | 80.50 |
Обратите внимание, что значения для столбцов attendees_num и price заключены в кавычки, то есть они являются строковыми значениями. Поскольку столбец attendees_num имеет целочисленный тип, а значение предоставляется в виде строки, SQL неявно преобразует строку в целое число перед ее вставкой в таблицу. Таким же образом, поскольку столбец price имеет тип decimal, SQL неявно преобразует строковое значение цены в decimal перед вставкой в таблицу.
Неявное преобразование также может происходить при сравнении значений или выполнении арифметических операций в запросах SQL. В таком случае SQL попытается неявно преобразовать значения в общий тип данных перед выполнением операции.
Приведем пример подобной конвертации:
SELECT attendees_num * price AS total_price FROM meetings;
| total_price |
|---|
| 9660.00 |
В примере выше мы умножаем значения столбцов attendees_num и price и даем столбцу с результирующей суммой псевдоним total_price.
Поскольку столбцы attendees_num и price имеют разные типы данных (int и decimal, соответственно), SQL неявно преобразует одно из значений в общий тип данных перед выполнением операции умножения. В таком случае SQL преобразует attendees_num в decimal.
Есть определенное правило, по которому происходит выбор приоритета для преобразования типов при проведении над ними арифметических операций либо операций сравнения. Это правило гласит, что тип, имеющий меньший приоритет, будет конвертироваться в тип с бОльшим приоритетом. Так как тип данных decimal имеет приоритет над int, в примере выше конвертация происходила именно в decimal.
Таким образом, SQL производит преобразование типов по определенным правилам с учетом приоритетов типов. Однако конвертация не всегда может происходить так, как мы от нее ожидаем, что может вызывать непредвиденные результаты и ошибки. Для большего контроля над процессом конвертации типов лучше пользоваться явным преобразованием. В этом нам помогут функции CAST и CONVERT, о которых речь пойдет далее в статье.
Функция CAST SQL
Функция CAST SQL используется для преобразования данных из одного типа в другой при выполнении определенной операции или сравнения.
Синтаксис функции CAST в СУБД MySQL прост:
CAST(expression AS datatype)
В этой записи expression — значение или столбец для преобразования, а datatype — целевой тип данных. В MySQL типов datatype могут быть следующие:
BINARY[(N)]CHAR[(N)]DATEDATETIMEDECIMAL[(M[,D])]SIGNED [INTEGER]TIMEUNSIGNED [INTEGER]
Например, воспользуемся функцией CAST, чтобы вывести цену в формате целочисленного значения (SIGNED) из столбца с типом данных decimal:
SELECT CAST(price AS SIGNED) as price_signed FROM meetings;
| price_signed |
|---|
| 81 |
В столбце price хранится значение 80.50 типа decimal. При конвертации в целое число получим 81.
Приведем пример преобразования даты в строку. Выберем из таблицы начальные и конечные даты митинга, произведем конвертацию каждой из них в строковый тип CHAR(20) и добавим дополнительные строки From и to:
SELECT
CONCAT(
'From ',
CAST(start_date_time as CHAR(20)),
' to ',
CAST(end_date_time as CHAR(20))
) as start_end
FROM
meetings; | start_end |
|---|
| From 2023-03-23 09:00:00 to 2023-03-23 10:00:00 |
Важно отметить, что функция CAST может выполнять преобразование только между совместимыми типами данных. Например, вы можете преобразовать целое число в строку, но вы не можете преобразовать строку в целое число, если строка содержит нечисловые символы. Кроме того, вы также должны знать о возможном усечении данных при использовании функции CAST. Если целевой тип данных имеет меньшую максимальную длину, чем исходный тип данных, данные могут быть усечены в процессе преобразования.
Подкорректируем предыдущий пример, установив тип конвертации CHAR(10), то есть длина строки не должна быть больше 10. В итоге получим усечение значений строк дат до 10 символов:
SELECT
CONCAT(
'From ',
CAST(start_date_time as CHAR(10)),
' to ',
CAST(end_date_time as CHAR(10))
) as start_end
FROM
meetings; | start_end |
|---|
| From 2023-03-23 to 2023-03-23 |
Таким образом, следует внимательно выбирать тип данных, в которые необходимо конвертировать значение, чтобы избежать получения некорректного результата.
В MySQL есть еще одна функция преобразования типов данных — CONVERT, о которой поговорим далее.
Функция CONVERT SQL
Как и функция CAST, функция CONVERT SQL также используется для преобразования данных в другой тип. Синтаксис функции CONVERT в MySQL выглядит так:
CONVERT(expression, datatype)
Эта функция принимает два параметра: expression — значение или столбец, который вы хотите преобразовать, datatype — целевой тип данных.
Функция CONVERT выполняет такие же задачи, как и CAST. Синтаксис CAST(expression AS datatype) идентичен синтаксису CONVERT(expression, datatype). Например, выберем из нашей таблицы количество участников митинга в формате строки. Эти два запроса будут работать одинаково:
SELECT CONVERT(attendees_num, CHAR(10)) AS attendees_num_string FROM meetings; SELECT CAST(attendees_num AS CHAR(10)) AS attendees_num_string FROM meetings;
В целом, можно отметить, что функции CAST и CONVERT работают идентично, но имеют небольшие отличия в синтаксисе. К примеру, с помощью CAST можно изменить кодировку значения таким образом:
SELECT CAST(title AS CHAR CHARACTER SET utf8mb4) from meetings;
В свою очередь, CONVERT имеет два способа смены кодировки:
SELECT CONVERT(title, CHAR CHARACTER SET utf8mb4) from meetings; SELECT CONVERT(title USING utf8mb4) from meetings;
Заключение
Резюмируя, отметим, что преобразование типов данных в SQL может происходить явным и неявным способом. Чтобы иметь больший контроль над изменением типов, рекомендуется использовать явное преобразование с помощью функций CAST SQL и CONVERT SQL. В MySQL эти функции выполнять одни и те же задачи и отличаются синтаксисом.