Синтаксис MySQL CREATE TABLE для разработчиков T-SQL

Добавил Sergey Moiseenko on Суббота, 10 апреля. 2021

Пересказ статьи Edwin Sanchez. Top 5 MySQL CREATE TABLE Syntax for T-SQL Developers

Вы разработчик T-SQL, изучающий основы MySQL? Тогда одной из вещей, которую вы захотите узнать, это CREATE TABLE в MySQL. Кроме всего прочего, наискорейший способ изучить новую платформу баз данных SQL, это сравнение общей функциональности и синтаксиса.

Именно этим мы собираемся сейчас заняться. Но полный синтаксис - это слишком много. Поэтому мы только рассмотрим 5 основных пунктов, которые позволят вам освоить и выполнять оператор CREATE TABLE в MySQL.

Однако, прежде чем продолжить, давайте проясним несколько моментов:

Здесь рассматривается версия MySQL 8, использующая механизм хранения InnoDB.

Используемая версия SQL Server - 2019.

Подходит? Тогда начнем.

1. Синтаксис идентификаторов MySQL CREATE TABLE

Начнем сравнение с определения имени таблицы.

MySQL не имеет имени схемы в определении таблицы

Одним из заметных различий в синтаксисе MySQL CREATE TABLE является отсутствие имени схемы. Вот как это выглядит:

CREATE TABLE database_name.table_name

Вы заметили, что имени таблицы предшествует имя базы данных? А вот знакомый вам эквивалентный формат T-SQL:

CREATE TABLE database_name.schema_name.table_name

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

MySQL использует обратные кавычки вместо квадратных скобок в именах таблиц и столбцов

Это так. Использование квадратных скобок в именах таблиц и столбцов вызовет ошибку в MySQL. Вместо них следует использовать пару обратных кавычек для ограничителей идентификаторов. На рисунке показано, где их найти клавиатуре:

Теперь проверьте пример:

-- MySQL CREATE TABLE

CREATE TABLE `testdatabase`.`person` (

  `BusinessEntityID` INT NOT NULL,

  `PersonType` NCHAR(2) NOT NULL,

  `Title` VARCHAR(8) NULL,

  `FirstName` NVARCHAR(50) NOT NULL,

  `MiddleName` VARCHAR(50) NULL,

  `LastName` VARCHAR(50) NOT NULL,

  `Suffix` NVARCHAR(10) NULL,

  `EmailPromotion` INT NOT NULL,

  `ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),

  PRIMARY KEY (`BusinessEntityID`));

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

Вот эквивалентный код в T-SQL:

-- T-SQL CREATE TABLE

CREATE TABLE [testdatabase].[dbo].[Person](

	[BusinessEntityID] [int] NOT NULL,

	[PersonType] [nchar](2) NOT NULL,

	[Title] [nvarchar](8) NULL,

	[FirstName] [nvarchar](50) NOT NULL,

	[MiddleName] [nvarchar](50) NULL,

	[LastName] [nvarchar](50) NOT NULL,

	[Suffix] [nvarchar](10) NULL,

	[EmailPromotion] [int] NOT NULL,

	[ModifiedDate] [datetime] NOT NULL DEFAULT GETDATE(),

 CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY  

  (

	[BusinessEntityID] ASC

  )

) ON [PRIMARY]

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

MySQL использует "TEMPORARY" вместо '#' для временных таблиц

Знаете о временных таблицах? Тогда для определения временной таблицы в MySQL вам придется больше печатать. Проверьте пример:

-- Временная таблица в MySQL

CREATE TEMPORARY TABLE `MyTempTable`

Между тем, программисты T-SQL используют символ # перед именем именем таблицы. Вот эквивалентный код:

-- Временная таблица в T-SQL

CREATE TABLE #MyTempTable

Мы разобрались с определением таблицы и именами. Перейдем к определению столбцов.

2. Синтаксис определения столбца в MySQL

Не может быть базовой таблицы SQL без столбцов. Итак, что общего и какие различия имеются между MySQL и SQL Server?

Имена столбцов и типы данных

Вы уже знаете об обратных кавычках. Все остальное, что касается имен столбцов и типов данных, одинаково в MySQL и T-SQL. См. пример ниже:

CREATE TABLE `testdatabase`.`people`

(

    `ID` INT,

    `LastName` VARCHAR(50),

    ...

);

Однако это еще не все.

Столбцы, допускающие NULL

Столбцы могут допускать NULL или не допускать. Вы можете установить это, добавив NULL или NOT NULL в определение столбца.

Давайте расширим наш предыдущий пример:

CREATE TABLE `testdatabase`.`people`

(

     `ID` INT NOT NULL,

     `LastName` VARCHAR(50) NOT NULL,

     `FirstName` VARCHAR(50) NOT NULL,

     `MiddleName` VARCHAR(50) NULL,

     ...

);

Это почти то же, что и в T-SQL. А что со значениями по умолчанию?

Значения по умолчанию столбца

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

CREATE TABLE `testdatabase`.`people`

(

     `ID` INT NOT NULL,

     `LastName` VARCHAR(50) NOT NULL,

     `FirstName` VARCHAR(50) NOT NULL,

     `MiddleName` VARCHAR(50) NULL,

     `ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),

     ...

);

Для столбца ModifiedDate значением по умолчанию является текущая системная дата и время. Если вы выполните оператор INSERT для этой таблицы, подобный представленному ниже, вы всегда будете получать это значение для ModifiedDate.

INSERT INTO testdatabase.people

(ID, LastName, FirstName, MiddleName)

VALUES

(1,'Kirk','James','Tiberius');

Это работает, как и ожидалось после INSERT. Ниже приведен скриншот из dbForge Studio для MySQL:

3. Синтаксис ограничений таблицы в MySQL

Как и в любой платформе SQL, вы можете в MySQL определить правила для данных в столбце таблицы, используя ограничения. Рассмотрим этот важный аспект проектирования таблиц.

Разработчик T-SQL должен почувствовать себя как дома, имея дело с первичными ключами, внешними ключами и уникальными ключами в MySQL.

Первичные ключи

Вы можете определить один или несколько столбцов в качестве первичного ключа. Синтаксис почти тот же, что и в SQL Server. Есть ли исключения? Да. Это использование кластеризованных и некластеризованных индексов. Но давайте отложим их обсуждение до следующего раздела.

Теперь же остановимся на синтаксисе первичных ключей. Ниже тот же пример, который мы рассматривали ранее, но мы добавим столбец ID в качестве первичного ключа:

CREATE TABLE `testdatabase`.`people`

(

     `ID` INT NOT NULL PRIMARY KEY,

     `LastName` VARCHAR(50) NOT NULL,

     `FirstName` VARCHAR(50) NOT NULL,

     `MiddleName` VARCHAR(50) NULL,

     `ModifiedDate` DATETIME NOT NULL DEFAULT NOW()

);

Легко, не так ли?

Но если вам требуется несколько столбцов? Как и в случае T-SQL, у вас есть 2 варианта. Посмотрите примеры ниже:

-- Вариант 1: Пусть MySQL сгенерирует имя ограничения



CREATE TABLE `testdatabase`.`people`

(

     `ID` INT NOT NULL,

     `LastName` VARCHAR(50) NOT NULL,

     `FirstName` VARCHAR(50) NOT NULL,

     `MiddleName` VARCHAR(50) NOT NULL DEFAULT '',

     `ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),

     PRIMARY KEY (`LastName`,`FirstName`,`MiddleName`)

);



-- Вариант 2: Задание имени ограничения



CREATE TABLE `testdatabase`.`people`

(

     `ID` INT NOT NULL,

     `LastName` VARCHAR(50) NOT NULL,

     `FirstName` VARCHAR(50) NOT NULL,

     `MiddleName` VARCHAR(50) NOT NULL DEFAULT '',

     `ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),

    CONSTRAINT `pk_name` PRIMARY KEY (`LastName`, `FirstName`, `MiddleName`)

);

Это все для первичных ключей.

Внешние ключи

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

CREATE TABLE `testdatabase`.`address`

(

    `ID` int NOT NULL PRIMARY KEY,

    `parent_id` int NOT NULL,

    `full_address` varchar(100) NOT NULL,

    CONSTRAINT `FK_address_parent_id` FOREIGN KEY (`parent_id`)

    REFERENCES `people` (`ID`)

);

Видите подобие с T-SQL?

Уникальные ключи

Иногда требуется гарантировать уникальность данных вставляемых в таблицу. MySQL также поддерживает уникальные ключи. Вот пример:

CREATE TABLE `testdatabase`.`people`

(

     `ID` INT NOT NULL,

     `LastName` VARCHAR(50) NOT NULL,

     `FirstName` VARCHAR(50) NOT NULL,

     `MiddleName` VARCHAR(50) NOT NULL DEFAULT '',

     `ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),

    CONSTRAINT `PK_people_id` PRIMARY KEY (`ID`),

    CONSTRAINT `IDX_name` UNIQUE KEY (`LastName`,`FirstName`,`MiddleName`)

);

Этот код гарантирует, что только уникальные имена могут быть добавлены в таблицу.

Кластеризованный и некластеризованный индекс в MySQL

В предыдущих примерах вы видели создание первичных и внешних ключей. Но где кластеризованные и некластеризованные индексы? Это бросается в глаза, поскольку в SQL Server вы явно указываете ключевое слово CLUSTERED, чтобы создать кластеризованный индекс.

Интересный момент, что в MySQL нет ключевых слов CLUSTERED или NONCLUSTERED. Как тогда создавать кластеризованные индексы?

Легко. Вы просто указываете столбцы первичного ключа, и механизм хранение InnoDB создаст кластеризованный индекс. В примере ниже ID является первичным ключом И кластеризованным индексом.

CREATE TABLE `testdatabase`.`people`

(

     `ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

     `LastName` VARCHAR(50) NOT NULL,

     `FirstName` VARCHAR(50) NOT NULL,

     `MiddleName` VARCHAR(50) NOT NULL DEFAULT '',

     `ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),

    CONSTRAINT `IDX_name` UNIQUE KEY (`LastName`,`FirstName`,`MiddleName`)

);

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

И опять, если вы не имеете первичного ключа и уникального индекса, InnoDB создает скрытый кластеризованный индекс. Доказательство тут.

Следующий вопрос, а что насчет некластеризованного индекса?

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

В следующем разделе обсуждается уникальный синтаксис для MySQL CREATE TABLE, которого нет в T-SQL.

5. Синтаксис клонирования и копирования таблиц в MySQL

Давайте обсудим клонирование таблиц. Мы можем это сделать в MySQL CREATE TABLE. Затем покажем эквивалент в T-SQL.

В примере ниже с помощью CREATE TABLE...LIKE создается таблица такой же структуры, как и первая таблица.

CREATE TABLE `people2` LIKE `people`

Мы просто клонировали таблицу people в таблицу people2. Однако копируется только структура таблицы people, но не данные. При этом копируются ограничения ключей и индексы оригинальной таблицы. Довольно полезно на мой взгляд.

Наиболее близкий эквивалент T-SQL выглядит так:

-- Способ клонирования таблицы в T-SQL. 

-- Однако индексы и ограничения ключей не включаются.

SELECT * INTO people2

FROM people

WHERE 1=0     -- Для того, чтобы SELECT не возвращал строк.

А если захотите данные? Тогда ответ CREATE TABLE...SELECT:

CREATE TABLE `people3` AS

SELECT * FROM `people`;

Все данные из таблицы people будут скопированы в таблицу people3. Однако эта команда не включает индексов и ограничений.

Наиболее близкий эквивалент синтаксиса в T-SQL:

-- Копирование таблицы в T-SQL

SELECT * INTO people3 

FROM people

Заключение

Я надеюсь, что эти заметки познакомили вас, как разработчика T-SQL, с оператором CREATE TABLE в MySQL и позволят вам без проблем выполнять его при необходимости.

Некоторые предпочтут использовать графические инструменты. Но позже вы будете разыскивать ссылки, когда потребуется создавать рабочие таблицы в ваших хранимых процедурах или пакетах. Или вы просто из тех, кто хочет контролировать то, что он печатает. В любом случае, сравнение базового синтаксиса по принципу «одного окна» может оказаться полезным.

Обратные ссылки

URI этой записи для создания обратных ссылок (trackback)

Нет обратных ссылок

Добавить комментарий

Имя

Домашняя страница

Комментарий

В ответ на

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.

Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

Enter the string from the spam-prevention image above:

Form options

Запомнить информацию?

Добавленные комментарии должны будут пройти модерацию прежде, чем будут показаны.

SQL-Ex blog