- Как очистить таблицу в MySQL
- Как очистить таблицу в MySQL
- 1. Удаление данных таблицы с помощью DELETE
- 2. Удаление данных таблицы с помощью TRUNCATE
- Как очистить таблицу с Foreign Key Constraint
- 1. Отключение проверки внешних ключей
- 2. Добавление опции ON DELETE CASCADE
- Выводы
- Создание и удаление таблиц
- Удалить все данные из таблицы
- ПРИМЕРЫ создания таблиц
Как очистить таблицу в MySQL
В MySQL, как и в других СУБД можно очищать таблицы. Очистка таблицы позволяет удалять данные при этом не затрагивая саму структуру таблицы. В MySQL существует несколько способов очистки таблицы. В частности, можно выделить очистку таблицы при помощи команд DELETE и TRUNCATE .
Обе команды выполняют одну и ту же задачу, но имеют несколько отличий о которых будет рассказано далее в статье. Также будет упомянуто об удалении данных из таблицы при наличии внешних ключей ( foreign key ). В данной статье будет рассмотрено как очистить таблицу в MySQL различными способами в операционной системе Ubuntu 20.04.
Как очистить таблицу в MySQL
Для очистки таблицы в MySQL существует несколько способов. Далее будут рассмотрены все возможные способы.
1. Удаление данных таблицы с помощью DELETE
Для удаления данных из таблицы можно воспользоваться инструкцией DELETE которая может удалять строки таблицы по заданному условию. Предположим, есть таблица MyGuests в которой есть пользователь John:
При помощи оператора DELETE и заданного условия — в данном случае удаление будет происходит по столбцу firstname который принимает значение имени пользователя будет удалена запись с номером (id) 1 и именем John:
DELETE FROM MyGuests WHERE firstname = ‘John’;
Также оператор DELETE может удалять все строки таблицы сразу. В качестве примера есть таблица с двумя записями:
Удалим все строки за один раз не задавая никаких условий. Для этого необходимо выполнить следующий SQL запрос:
На этом очистка таблицы MySQL завершена.
2. Удаление данных таблицы с помощью TRUNCATE
Также для удаления всех строк в таблице существует специальная команда TRUNCATE. Она схожа с DELETE, однако он не позволяет использовать WHERE. Также стоит выделить следующие особенности при очистки таблицы MySQL с помощью оператора TRUNCATE:
- 1) TRUNCATE не позволяет удалять отдельные строки;
- 2) DELETE блокирует каждую строку, а TRUNCATE всю таблицу;
- 3) TRUNCATE нельзя использовать с таблицами содержащими внешние ключи других таблиц;
- 4) После использования оператора TRUNCATE в консоль не выводится информация о количестве удаленных строк из таблицы.
В качестве примера возьмем таблицу с двумя записями из предыдущего примера:
Для очистки этой таблицы MySQL от всех записей необходимо выполнить следующий SQL запрос:
Как уже было упомянуто ранее команда TRUNCTE не выводит количество удалённых строк в таблице поэтому в консоль был выведен текст 0 rows affected.
Как очистить таблицу с Foreign Key Constraint
Если в таблице присутствуют внешние ключи (Foreign Key) то просто так очистить таблицу не получится. Предположим, есть 2 таблицы — Equipment и EquipmentCategory:
В таблице Equipment присутствует столбец с именем category_id, который связан внешним ключом со столбцом id в другой таблице — EquipmentCategory. Например:
Если попытаться очистить все строки таблицы EquipmentCategory при помощи оператора TRUNCATE то будет выведена следующая ошибка:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`Inventory`.`Equipment`, CONSTRAINT `Equipment_ibfk_1`)
Данная ошибка говорит о том, что таблица ссылается на другую таблицу и имеет ограничение на удаление в виде внешнего ключа. Чтобы избежать данной ошибки можно воспользоваться отключением проверки внешних ключей и добавлением параметра ON DELETE CASCADE при создании таблицы.
1. Отключение проверки внешних ключей
Чтобы очистить таблицу при наличии в ней внешних ключей можно отключить проверку внешних ключей. Для этого нужно выполнить следующую команду:
Данная команда отключит проверку внешних ключей и тем самым позволит очистить таблицу при помощи команды TRUNCATE:
Также очистить таблицу можно и при помощи DELETE:
После очистки таблицы можно вернуть проверку внешних ключей при помощи команды:
Однако такой способ использовать не рекомендуется, потому что вы теряете консистентность данных в базе данных и программа использующая её может работать некорректно. Есть и другое решение. При удалении таблицы удалять все связанные с ней записи в других таблицах.
2. Добавление опции ON DELETE CASCADE
Опция ON DELETE CASCADE используется для неявного удаления строк из дочерней таблицы всякий раз, когда строки удаляются из родительской таблицы. Опция ON DELETE CASCADE её можно задать при создании таблицы, однако если вы этого не сделали, то можно удалить CONSTRAINT и создать его заново.
Для просмотра информации о внешнем ключе содержащемся в таблице необходимо выполнить команду SHOW CREATE TABLE:
SHOW CREATE TABLE EquipmentCategory;
В данном примере таблица называется EquipmentCategory и в ней присутствует константа с именем EquipmentCategory_ibfk_1.
Далее необходимо удалить внешний ключ при помощи команд ALTER TABLE и DROP . Например, если EquipmentCategory — имя таблицы, а EquipmentCategory_ibfk_1 — имя внешнего ключа выполните:
ALTER TABLE EquipmentCategory DROP FOREIGN KEY EquipmentCategory_ibfk_1;
После этого внешний ключ необходимо вернуть обратно добавив к нему опцию ON DELETE CASCADE. Команда будет следующей :
ALTER TABLE EquipmentCategory ADD FOREIGN KEY (category_id) references EquipmentCategory(id) on DELETE CASCADE;
После этого таблицу можно очистить при помощи команды TRUNCATE :
Выводы
В данной статье было рассмотрено как очистить таблицу MySQL от данных. Были рассмотрены команды DELETE и TRUNCATE а также созданы таблицы с наполненной информацией для демонстрации удаления информации. Если у вас остались вопросы задавайте их в комментариях!
Обнаружили ошибку в тексте? Сообщите мне об этом. Выделите текст с ошибкой и нажмите Ctrl+Enter.
Создание и удаление таблиц
CREATE TABLE name_table(
id INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL ,
title VARCHAR(255) ,
message TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
age INT(11) DEFAULT 0 ,
price DOUBLE
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
PRIMARY KEY — первичный ключ, ограничение, позволяющее однозначно идентифицировать каждую запись в таблице SQL.
Первичные ключи должны содержать уникальные значения.
Первичный ключ не может иметь значения NULL.
NOT NULL — означает, что каждая строка должна содержать значение для этого столбца, нулевые значения допускаться не будут.
AUTO_INCREMENT — автоматический инкремент.
CHARACTER SET utf8 COLLATE utf8_general_ci — кодировка для конкретного поля, ставится сразу после типа.
). — здесь кодировка задана для всех полей
DEFAULT — значение по умолчанию.
ENGINE=InnoDB — тип таблицы(используемый движок) — InnoDB
Пример запроса на удаление таблицы name_table :
$sql = «DROP TABLE name_table» ;
Удалить все данные из таблицы
— данная команда очистит полностью таблицу и приведет ее к первоначальному состоянию,
когда она только создалась ( CREATE TABLE ) и в ней не было никаких записей.
— данная команда не производит сброс счётчика AUTO_INCREMENT , который используется, как известно, для автоматической генерации значения поля без необходимости его ручной установки. Чтобы этого избежать, можно использовать вместо команды выше данный вариант:
TRUNCATE TABLE name_table RESTART IDENTITY ;
— данный вариант вызова команды TRUNCATE позволит сбросить значение счётчика полей с AUTO_INCREMENT .
Поэтому значение поля первой добавленной записи после данного удаления будет 1 .
ПРИМЕРЫ создания таблиц
1 mysqli — объектно-ориентированный подход
// Создаем переменные
$servername = «localhost» ; // для локального сервера на компьютере
$username = «root» ; // имя пользователя
$password = «» ; // пароль если существует
$dbname = «newDB» ; // имя базы данных
// Устанавливаем соединение с сервером MySQL:
// создаем экземпляр класса new mysqli() и записываем, в качестве аргументов, наши переменные
$conn = new mysqli ( $servername , $username , $password , $dbname );
// Проверка соединения:
// если выполняется выражение $conn->connect_error , то останавливаем выполнение die
// и выводим «Не удалось подключиться»
// С помощью выражения $conn -> connect_error выведем информацию об ошибке .
// Иначе выведем: «Соединение успешно установлено»
if ( $conn -> connect_error ) die ( «Не удалось подключиться: » . $conn -> connect_error );
>
echo «Соединение успешно установлено» ;
// Запрос для создания таблицы users1
$sql = «CREATE TABLE users(
name VARCHAR(30) NOT NULL,
surname VARCHAR(30) NOT NULL PRIMARY KEY,
password VARCHAR(30) NOT NULL )» ;
// Проверка выполнения создания таблицы:
// Если значение $conn -> query($sql) будет — TRUE,
// тогда выведем сообщение «Таблица users создана успешно».
// В противном случае выведем сообщение «Ошибка при создании таблицы»
// и выведем информацию об ошибке: $conn->error.
if ( $conn -> query ( $sql ) === TRUE ) echo «Таблица users создана успешно
» ;
> else <
echo «Ошибка при создании таблицы: » . $conn -> error ;
>
// закрываем соединение
$conn -> close ();
?>
— После перезагрузки страницы выведет в браузере:
Таблица users создана успешно
В phpMyAdmin в базе данных newDB появилась таблица » users «
2 mysqli — процедурный подход
// Создаем переменные
$servername = «localhost» ; // для локального сервера на компьютере
$username = «root» ; // имя пользователя
$password = «» ; // пароль если существует
$dbname = «newDB» ; // имя базы данных
// Устанавливает соединение с сервером MySQL с помощью функции mysqli_connect()
$conn = mysqli_connect ( $servername , $username , $password , $dbname );
if (! $conn ) die ( «Не удалось подключиться: » . mysqli_connect_error ());
>
echo «Соединение успешно установлено» ;
// Запрос для создания таблицы:
// NOT NULL — означает, что каждая строка должна содержать значение для этого столбца,
// нулевые значения допускаться не будут.
// PRIMARY KEY — первичный ключ, ограничение, позволяющее однозначно идентифицировать
// каждую запись в таблице SQL.
$sql = «CREATE TABLE users2(
name VARCHAR(30) NOT NULL,
surname VARCHAR(30) NOT NULL PRIMARY KEY, password VARCHAR(30) NOT NULL )» ;
// Проверка выполнения создания таблицы:
// Если запрос к базе данных — выполнен,
// тогда выведем сообщение «Таблица users2 создана успешно».
// В противном случае выведем сообщение «Ошибка при создании таблицы»
// и выведем информацию об ошибке mysqli_error($conn) ;
if ( mysqli_query ( $conn , $sql )) echo «Таблица users2 создана успешно
» ;
> else <
echo «Ошибка при создании таблицы: » . mysqli_error ( $conn ); >
// закрываем соединение
mysqli_close ( $conn );
?>
— После перезагрузки страницы выведет в браузере:
Таблица users2 создана успешно
В phpMyAdmin в базе данных newDB появилась таблица » users2 «
// Создаем переменные
$servername = «localhost» ; // для локального сервера на компьютере
$username = «root» ; // имя пользователя
$password = «» ; // пароль если существует
$dbname = «newDB» ; // имя базы данных
// Подключение к серверу MySQL
// Обработка ошибок подключения: используем блок try-catch :
try // Подключение к серверу MySQL
$conn = new PDO ( «mysql:host= $servername ; dbname= $dbname » , $username , $password );
echo «Соединение успешно установлено
» ;
// Устанавливаем режим ошибки PDO в исключение
$conn -> setAttribute ( PDO :: ATTR_ERRMODE , PDO :: ERRMODE_EXCEPTION );
> catch ( PDOException $e ) <
echo «Ошибка при создании таблицы:
» . $e -> getMessage ();
>
// Созданние таблицы
try // Созданние SQL-запроса
$sql = «CREATE TABLE users3(
name VARCHAR(30) NOT NULL,
surname VARCHAR(30) NOT NULL PRIMARY KEY,
password VARCHAR(30) NOT NULL )» ;
// Устанавливаем режим ошибки PDO в исключение
$conn -> setAttribute ( PDO :: ATTR_ERRMODE , PDO :: ERRMODE_EXCEPTION );
$conn -> exec ( $sql );
echo «Таблица users3 создана успешно
» ;
> catch ( PDOException $e ) <
echo «Ошибка при создании таблицы:
» . $sql . $e -> getMessage ();
>
// Закрыть подключение
$conn = null ;
?>
— После перезагрузки страницы выведет в браузере:
Таблица users3 создана успешно
В phpMyAdmin в базе данных newDB появилась таблица » users3 «