Mysql exists: MySQL | Оператор EXISTS

Решение для ошибки MySQL-запроса IF EXISTS UPDATE ELSE INSERT

При работе со связанными таблицами, когда требуется создать новую запись в связанной таблице, если её ещё нет и обновить поля в этой записи, если она уже есть, требуется написать MySQL-запрос , выполняющий данные условия. Однако, при работе со связанными таблицами InnoDB могут возникнуть проблемы в виде ошибки корректности использования условного оператора IF, а также проверки EXISTS. Решая данную задачу, я пришёл к выводу, что нужно использовать конструкцию  ON DUPLICATE KEY UPDATE, которая решает данную задачу без ошибок.

Ошибка при использовании MySQL-запроса IF EXISTS UPDATE ELSE INSERT

Итак, имеем две таблицы. Основная tableA и связанная с ней tableВ по полю id. Казалось бы логично описать логику работы следующим SQL-запросом:

IF EXISTS(SELECT * FROM `tableB` WHERE id = `тут id ключа`) 
   UPDATE `tableB`
      SET var_1 = `значение1`, var_2 = `значение2` //. .и т.д. для обновления тех полей, значения которых нужно обновить
    WHERE id = `тут id ключа` // в записи, содержащей ключ id
ELSE 
    INSERT INTO `tableB` (`id`, `var_1`, `var_2`)
    VALUES ('тут id ключа', 'значение1', 'значение2')

Но, при попытке выполнить такой SQL-запрос сыплются ошибки и жалобы, то на неправильное применение условного оператора IF, то какие-то проблемы рядом с проверкой EXISTS. Все попытки привести данный запрос к работающему виду у меня не привели ни к какому результату. Поэтому вместо него я попробовал другую конструкцию SQL-запроса, выполняющего эту же логику, но без ошибок отрабатывающего необходимые действия.

Альтернативный MySQL-запрос для обновления или добавления записи в таблицу со связанным полем с другой таблицей

Итак, вся эта задача сводится к тому, чтобы в связанной таблице tableВ содержались записи с уникальными ключами id. И если в ней уже есть запись с этим ключом, то не создавать ещё одну запись, а обновить поля в имеющейся. Используем следующий SQL-запрос, содержащий функцию работы с ключами ON DUPLICATE KEY UPDATE:

INSERT INTO `tableB` (`id`,`var_1`,`var_2`)
  VALUES ('тут id ключа','значение1','значение2') // и это отработает, если id в таблице tableB ещё нет
ON DUPLICATE KEY UPDATE //а то, что ниже, просто обновит запись, содержащую id
  `var_1` = 'значение1',
  `var_2` = 'значение2'

И этот запрос обрабатывается правильно.

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

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

  `var_1` = `var_1` + 'значение1',
  `var_2` = `var_2` + 'значение2'

Таким образом, можно разгрузить основную таблицу tableA, не храня значения счётчиков в ней, а вынести эти счётчики в отдельную связанную по ключу таблицу tableB. И дело не только в том, что в основной таблице станет меньше полей, а ещё и в том, что если для работы требуется чаще значения счётчиков, то и запросы в более короткой таблице, содержащие значения этих счётчиков будут получены быстрее и проще.

Мне это понадобилось для разделения таблицы, содержащей список транзакций пользователей (tableA) и таблицы, содержащей состояние счёта пользователей (tableB) при написании биллинга на сайте, работающем на CMS Joomla. Но данное решение может быть нужно и для массы других задач, которые требуют раздельного хранения суммарных значений по конкретным позициям какого-либо объекта.

Заберите ссылку на статью к себе, чтобы потом легко её найти 😉

Выберите, то, чем пользуетесь чаще всего:

Спасибо за внимание, оставайтесь на связи! Ниже ссылка на форум и обсуждение ; )

Как устранить неисправность InnoDB в базе данных MySQL — База знаний JustHost.ru

Источник: http://kb. odin.com/ru/6586

Проблема

1. MySQL query failed: Incorrect information in file: ‘./psa/misc.frm’
2. При работе mysqldump и mysqlcheck появляется сообщение о несуществующей таблице (для проверки используйте учетную запись администратора MySQL):

   mysqlcheck -uadmin -p****** db_example
   db_example.BackupTasks
   error : Can't find file: 'BackupTasks.MYD' (errno: 2)
   

3. Невозможно выполнить запрос таблицы с оператором «SELECT»:

   mysql> select * from db_example.misc;
   ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm'
   

4. Таблица не может быть восстановлена, так как ядро InnoDB не поддерживает восстановление.

   mysql> repair table misc;
   +-------------------------+--------+----------+---------------------------------------------------------+
   | Table | Op | Msg_type | Msg_text |
   +-------------------------+--------+----------+---------------------------------------------------------+
   | psa. APSApplicationItems | repair | note | The storage engine for the table doesn't support repair |
   +-------------------------+--------+----------+---------------------------------------------------------+
   

Причина

Повреждения InnoDB часто связаны с неисправностью оборудования. Сохранение поврежденных страниц происходит в результате сбоев питания или повреждений памяти. Также эта проблема может возникать, если вы храните базы данных InnoDB в сетевом хранилище (NAS).

Решение

Существует несколько способов восстановить MySQL:

I. Принудительное восстановление InnoDB

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

/etc/init.d/mysqld stop
mkdir /root/mysql_backup
cp -r /var/lib/mysql/* /root/mysql_backup/

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

/etc/my.cnf

[mysqld]
innodb_force_recovery = 4

ПРИМЕЧАНИЕ. Вы можете увеличить эту опцию до 5 или 6 — пока не получите оптимальный дамп.

Запустите службу mysqld:

/etc/init.d/mysqld start

Создайте дамп всех баз данных:

mysqldump -uadmin -p****** -A > /root/dumpall.sql

Если при создании дампа возникла следующая ошибка:
Incorrect information in file: ‘xxxxxxxx.frm’ when using LOCK TABLES»`

увеличьте значение innodb_force_recovery и повторите попытку. Если вы не можете создать дамп баз данных, попробуйте использовать способ II (скопировать содержимое таблицы) или III (восстановить из резервной копии).

Остановите mysqld и удалите поврежденные данные:

/etc/init.d/mysqld stop
rm -rf /var/lib/mysql/*

Удалите опцию innodb_force_recovery из файла /etc/my.cnf и запустите mysqld:

/etc/init. d/mysqld start

В результате этого будет восстановлена главная база данных «mysql» и движок баз данных InnoDB.
Восстановите базы данных из дампа:

mysql -uadmin -p****** > dumpall.sql

II. Копирование содержимого таблицы

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

/etc/init.d/mysqld stop
mkdir /root/mysql_backup
cp -r /var/lib/mysql/* /root/mysql_backup/

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

/etc/my.cnf

[mysqld]
innodb_force_recovery = 1

Попробуйте создать копию:

CREATE TABLE <новая таблица> LIKE <поврежденная таблица>;
INSERT INTO <новая таблица> SELECT * FROM <поврежденная таблица>;

Если получилось, удалите поврежденную таблицу и присвойте ее имя новой.

DROP TABLE <поврежденная таблица>;
RENAME TABLE <новая таблица> TO <поврежденная таблица>;

III. Восстановление таблицы InnoDB

Восстановление таблиц InnoDB необходимо в случае возникновения следующей ошибки

mysql> USE databasename;
mysql> SELECT * FROM table1;
ERROR 1146 (42S02): TABLE 'databasename.table1' doesn't exist
mysql>

Или при попытке сделать дамп через mysqldump

[red@hellsrv ~]$ mysqldump -uroot -p databasename > databasename.sql
Enter password:
mysqldump: Got error: 1146: Table 'databasename.table1' doesn't exist when using LOCK TABLES
[red@hellsrv ~]$

ВниманиеДо начала любых действий рекомендуем создать резервную копию файлов базы

Создать резервную копию через mysqldump не получится (из-за ошибки). Потребуется копирование файлов базы на уровне файловой системы:

service mysqld stop
cp -R /var/lib/mysql/databasename /home/USERNAME/backup

Для того чтобы восстановить таблицы InnoDB, нам нужно узнать:

• узнать структуру таблиц
• иметь файлы с данными (имеется ввиду файлы на уровне файловой системы)

Таблица InnoDB на уровне файловой системы состоит из двух фалов:

• файл . frm хранит в себе структуру таблицы;
• файл .ibd собственно данные

План восстановления:

• выяснить структуру поврежденной таблицы;
• создать новую базу;
• создать в новой базе таблицу нужной структуры;
• скопировать данные в новую таблицу из старой;
• если данные окажутся поврежденными, можно попробовать восстановить их используя утилиту innochecksum

Применяем утилиту чтения структуры таблицы:

mysqlfrm --diagnostic table1.frm
CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL comment 'ID',
  `title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Также желательно узнать кодировку старой базы:

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'databasename';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| cp1251                     | cp1251_general_ci      |
+----------------------------+------------------------+
1 ROW IN SET (0. 00 sec)

Создаем новую базу:

mysql> CREATE DATABASE helldb CHARACTER SET cp1251 DEFAULT COLLATE cp1251_general_ci;
Query OK, 1 ROW affected (0.00 sec)

Создаем таблицу по выводу утилиты чтения структуры поврежденной таблицы:

mysql> USE databasename;
mysql> CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL comment 'ID',
  `title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Далее копируем данные:

• Очищаем автоматически созданный файл

  mysql> ALTER TABLE tables1 DISCARD TABLESPACE;
  Query OK, 0 ROWS affected (0.04 sec)

• Копируем файл с данными с поврежденной таблицы

  cp /home/USERNAME/tables1.ibd tables1.ibd
  chown mysql:mysql tables1.ibd

• Импортируем данные

  mysql> ALTER TABLE tables1 IMPORT TABLESPACE;
  Query OK, 0 ROWS affected, 1 warning (0.50 sec)

• Проверяем корректность чтения данных

  mysql> SELECT * FROM tables1 LIMIT 10;
  +-----+-----------+
  | id  | title     |
  +-----+-----------+
  |  1  | Title 1   |
  |  2  | Title 2   |
  |  3  | Title 3   |
  |  4  | Title 4   |
  +-----+-----------+
  4 ROWS IN SET (0. 00 sec)

Далее можно импортировать восстановленную таблицу или базу целиком.

IV. Восстановление из резервной копии

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

Поделиться ссылкой:

Похожее

Зерезервированные слова в Mysql | MySQL

Подробности

Категория: MySQL

Просмотров: 2541

Если не проходит запрос к БД Mysql, то проверьте не попадает ли он под выражение «Зарезервированные слова».

Зарезервированные слова MySQL — те, что в таблице ниже нельзя использовать для имен ваших таблиц БД:

Зарезервированные слова, которые появились в Mysql версии 4. 1

Зарезервированные слова, которые появились в Mysql версии 5.0

Зарезервированные слова, которые появились в Mysql версии 5. 1

Зарезервированные слова, которые появились в Mysql версии 5.5

Зарезервированные слова, которые появились в Mysql версии 5.6

Новое зарезервированное слово добавилось Mysql в  5.7

В результате может возникать ошибка 1064 для Mysql

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

Как починть InnoDB в базе данных сервера MySQL

Повреждения InnoDB часто связаны с неисправностью оборудования. Сохранение поврежденных страниц происходит в результате сбоев питания или повреждений памяти. Также эта проблема может возникать, если вы храните базы данных InnoDB в сетевом хранилище (NAS).

Проблема

MySQL query failed: Incorrect information in file: ‘./psa/misc.frm’

При работе mysqldump и mysqlcheck появляется сообщение о несуществующей таблице (для проверки используйте учетную запись администратора MySQL):

# mysqlcheck -uadmin -p****** db_example
db_example.BackupTasks
error : Can't find file: 'BackupTasks.MYD' (errno: 2)

Невозможно выполнить запрос таблицы с оператором «SELECT»:

mysql> select * from db_example.misc;
ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm'

Таблица не может быть восстановлена, так как ядро InnoDB не поддерживает восстановление.

mysql> repair table misc;
+-------------------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+--------+----------+---------------------------------------------------------+
| psa. APSApplicationItems | repair | note | The storage engine for the table doesn't support repair |
+-------------------------+--------+----------+---------------------------------------------------------+

Решение

Существует несколько способов восстановить MySQL:

I. Принудительное восстановление InnoDB

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

# /etc/init.d/mysqld stop
# mkdir /root/mysql_backup
# cp -r /var/lib/mysql/* /root/mysql_backup/

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

[mysqld]
innodb_force_recovery = 4

ПРИМЕЧАНИЕ. Вы можете увеличить эту опцию до 5 или 6 — пока не получите оптимальный дамп.

Запустите службу mysqld:

# /etc/init.d/mysqld start

Создайте дамп всех баз данных:

# mysqldump -uadmin -p****** -A > /root/dumpall. sql

Если при создании дампа возникла следующая ошибка:

Incorrect information in file: ‘xxxxxxxx.frm’ when using LOCK TABLES»`

увеличьте значение innodb_force_recovery и повторите попытку. Если вы не можете создать дамп баз данных, попробуйте использовать способ II (скопировать содержимое таблицы) или III (восстановить из резервной копии).

Остановите mysqld и удалите поврежденные данные:

# /etc/init.d/mysqld stop
# rm -rf /var/lib/mysql/*

Удалите опцию innodb_force_recovery из файла /etc/my.cnf и запустите mysqld:

# /etc/init.d/mysqld start

В результате этого будет восстановлена главная база данных «mysql» и движок баз данных InnoDB.

Восстановите базы данных из дампа:

# mysql -uadmin -p****** > dumpall.sql

II. Копирование содержимого таблицы

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

# /etc/init. d/mysqld stop
# mkdir /root/mysql_backup
# cp -r /var/lib/mysql/* /root/mysql_backup/

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

[mysqld]
innodb_force_recovery = 1

Попробуйте создать копию:

CREATE TABLE <новая таблица> LIKE <поврежденная таблица>;
INSERT INTO <новая таблица> SELECT * FROM <поврежденная таблица>;

Если получилось, удалите поврежденную таблицу и присвойте ее имя новой.

DROP TABLE <поврежденная таблица>;
RENAME TABLE <новая таблица> TO <поврежденная таблица>;

III. Восстановление таблицы InnoDB

Восстановление таблиц InnoDB необходимо в случае возникновения следующей ошибки

mysql> USE databasename;
mysql> SELECT * FROM table1;
ERROR 1146 (42S02): TABLE 'databasename.table1' doesn't exist
mysql>

Или при попытке сделать дамп через mysqldump

# mysqldump -uroot -p databasename > databasename. sql
Enter password:
mysqldump: Got error: 1146: Table 'databasename.table1' doesn't exist when using LOCK TABLES


Внимание! До начала любых действий рекомендуем создать резервную копию файлов базы!

Создать резервную копию через mysqldump не получится (из-за ошибки). Потребуется копирование файлов базы на уровне файловой системы:

# service mysqld stop
# cp -R /var/lib/mysql/databasename /home/USERNAME/backup

Для того чтобы восстановить таблицы InnoDB, нам нужно узнать:

узнать структуру таблиц
иметь файлы с данными (имеется ввиду файлы на уровне файловой системы)

Таблица InnoDB на уровне файловой системы состоит из двух фалов:

файл .frm хранит в себе структуру таблицы;
файл .ibd собственно данные

План восстановления:

выяснить структуру поврежденной таблицы;
создать новую базу;
создать в новой базе таблицу нужной структуры;
скопировать данные в новую таблицу из старой;
если данные окажутся поврежденными, можно попробовать восстановить их используя утилиту innochecksum

Применяем утилиту чтения структуры таблицы:

# mysqlfrm --diagnostic table1. frm
CREATE TABLE `table1` (
`id` int(10) unsigned NOT NULL comment 'ID',
`title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Также желательно узнать кодировку старой базы:

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'databasename';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| cp1251 | cp1251_general_ci |
+----------------------------+------------------------+
1 ROW IN SET (0.00 sec)

Создаем новую базу:

mysql> CREATE DATABASE databasename CHARACTER SET cp1251 DEFAULT COLLATE cp1251_general_ci;
Query OK, 1 ROW affected (0.00 sec)

Создаем таблицу по выводу утилиты чтения структуры поврежденной таблицы:

mysql> USE databasename;
mysql> CREATE TABLE `table1` (
`id` int(10) unsigned NOT NULL comment 'ID',
`title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Далее копируем данные. Очищаем автоматически созданный файл:

mysql> ALTER TABLE tables1 DISCARD TABLESPACE;
Query OK, 0 ROWS affected (0.04 sec)

Копируем файл с данными с поврежденной таблицы:

# cp /home/USERNAME/tables1.ibd tables1.ibd
# chown mysql:mysql tables1.ibd

Импортируем данные:

mysql> ALTER TABLE tables1 IMPORT TABLESPACE;
Query OK, 0 ROWS affected, 1 warning (0.50 sec)

Проверяем корректность чтения данных:

mysql> SELECT * FROM tables1 LIMIT 10;
+-----+-----------+
| id | title |
+-----+-----------+
| 1 | Title 1 |
| 2 | Title 2 |
| 3 | Title 3 |
| 4 | Title 4 |
+-----+-----------+
4 ROWS IN SET (0.00 sec)

Далее можно импортировать восстановленную таблицу или базу целиком.

IV. Восстановление из резервной копии

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

Источник

MariaDB: Table ‘mysql.user’ doesn’t exist

Есть локальный сервер MariaDB на рабочем ноуте с Arch Linux, для мелких тестовых баз.

При попытке запуска — ошибка:

$ sudo systemctl start mariadb
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

Проверяем лог:

systemctl status mariadb

● mariadb.service — MariaDB database server

Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)

Active: failed (Result: exit-code) since Fri 2017-12-29 13:55:55 EET; 4min 53s ago

Process: 7405 ExecStart=/usr/bin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)

Process: 7349 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)

Process: 7348 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

Main PID: 7405 (code=exited, status=1/FAILURE)

Dec 29 13:55:55 setevoy-arch-work mysqld[7405]: 2017-12-29 13:55:55 140233913434048 [Note] Recovering after a crash using mysql-bin

Dec 29 13:55:55 setevoy-arch-work mysqld[7405]: 2017-12-29 13:55:55 140233913434048 [Note] Starting crash recovery. ..

Dec 29 13:55:55 setevoy-arch-work mysqld[7405]: 2017-12-29 13:55:55 140233913434048 [Note] Crash recovery finished.

Dec 29 13:55:55 setevoy-arch-work mysqld[7405]: 2017-12-29 13:55:55 140233913412352 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table ‘mysql.gtid_slave_pos’ doesn’t exist

Dec 29 13:55:55 setevoy-arch-work mysqld[7405]: 2017-12-29 13:55:55 140233913434048 [ERROR] Can’t open and lock privilege tables: Table ‘mysql.servers’ doesn’t exist

Dec 29 13:55:55 setevoy-arch-work mysqld[7405]: 2017-12-29 13:55:55 140233913434048 [Note] Server socket created on IP: ‘::’.

Dec 29 13:55:55 setevoy-arch-work mysqld[7405]: 2017-12-29 13:55:55 140233913434048 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.user’ doesn’t exist

Dec 29 13:55:55 setevoy-arch-work systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE

Dec 29 13:55:55 setevoy-arch-work systemd[1]: mariadb. service: Failed with result ‘exit-code’.

Dec 29 13:55:55 setevoy-arch-work systemd[1]: Failed to start MariaDB database server.

Удаляем всё содержимое /var/lib/mysql/ (при условии, что базы в MySQL — тестовые, иначе — сначала делаем бекап всех баз):

sudo rm -rf /var/lib/mysql/*

Устанавливаем дефолтные базы заново с помощью mysql_install_db:

sudo mysql_install_db —user=mysql —ldata=/var/lib/mysql/ —basedir=/usr/

Запускаем сервис:

systemctl start mariadb

Проводим обычную установку:

/usr/bin/mysql_secure_installation

Подключаемся:

mysql -u root -p

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 11

Server version: 10.1.29-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

Готово.

Все запрещённые/зарезервированные MySql слова

Зарезервированные слова в MySql версии 3.23:

В MySql версии 4. 0, список пополнился следующими словами:

В MySql версии 4.1, список пополнился следующими словами:

В MySql версии 5.0, список пополнился следующими словами:

В MySql версии 5. 1, список пополнился следующими словами:

В MySql версии 5.5, список пополнился следующими словами:

В MySql версии 5.6, список пополнился следующими словами:

В MySql версии 5.7, список пополнился следующим словом:

Комментарии и отзывы к материалу

Открыта вакансия на первого комментатора!

INSERT IF NOT EXISTS синтаксис »Автархия частной пещеры

18 октября 2007 г.

Для начала: в последней версии MySQL синтаксис, представленный в заголовке, невозможен. Но есть несколько очень простых способов добиться того, что ожидается, используя существующие функции.

Есть 3 возможных решения: с помощью INSERT IGNORE, REPLACE или INSERT… ON DUPLICATE KEY UPDATE.

Представьте, что у нас есть таблица:

1. CREATE TABLE `transcripts` (

2. ` ensembl_transcript_id` varchar (20) NOT NULL,

3. `transcript_chrom_start` int (10)

   NOT NULL

   `transcript_chrom_end` int (10) unsigned NOT NULL,

4. PRIMARY KEY (` ensembl_transcript_id`)

5. ) ENGINE = InnoDB DEFAULT CHARSET = latin1;

Теперь представьте, что у нас есть автоматический конвейер, импортирующий метаданные транскриптов из Ensembl, и что по разным причинам конвейер может выйти из строя на любом этапе выполнения.Таким образом, нам нужно убедиться в двух вещах: 1) повторное выполнение конвейера не приведет к разрушению нашей базы данных и 2) повторное выполнение не прекратится из-за ошибок «дублирования первичного ключа».

Метод 1: использование REPLACE

Это очень просто:

1. REPLACE INTO `transcripts`

2. SET` ensembl_transcript_id` = ‘ENSORGT00000000001’,

3. `transcript_312145_start transcript_chrom_end` = 12678;

Если запись существует, она будет перезаписана; если его еще нет, он будет создан.
Однако в нашем случае использование этого метода неэффективно: нам не нужно перезаписывать существующие записи, их можно просто пропустить.

Метод 2: использование INSERT IGNORE
Также очень просто:

1. INSERT IGNORE INTO `transcripts`

2. SET` ensembl_transcript_id` = ‘ENSORGT00000000001’,

0

42_start_chip 911 `transcript_chrom_end` = 12678;

Здесь, если «ensembl_transcript_id» уже присутствует в базе данных, он будет автоматически пропущен (проигнорирован).(Чтобы быть более точным, вот цитата из справочного руководства MySQL: «Если вы используете ключевое слово IGNORE, ошибки, возникающие при выполнении оператора INSERT, вместо этого обрабатываются как предупреждения. Например, без IGNORE, строка, которая дублирует существующий индекс UNIQUE. или значение PRIMARY KEY в таблице вызывает ошибку дублирования ключа, и выполнение оператора прерывается. ».) Если запись еще не существует, она будет создана.

У этого второго метода есть несколько потенциальных недостатков, включая невозможность прерывания запроса в случае возникновения любой другой проблемы (см. Руководство).Таким образом, его следует использовать, если ранее не было ключевого слова IGNORE.

Есть еще один вариант: использовать синтаксис INSERT… ON DUPLICATE KEY UPDATE, а в части UPDATE просто ничего не делать выполнить какую-то бессмысленную (пустую) операцию, например вычисление 0 + 0 (Джеффрей предлагает выполнить присвоение id = id чтобы механизм оптимизации MySQL игнорировал эту операцию). Преимущество этого метода в том, что он игнорирует только повторяющиеся ключевые события и по-прежнему прерывает выполнение других ошибок.

И последнее замечание: этот пост был вдохновлен Xaprb.Я также посоветовал бы проконсультироваться с другой его статьей о написании гибких SQL-запросов.

Эта запись была опубликована в четверг, 18 октября 2007 г., в 15:20 и находится в разделе «Программирование».
Вы можете следить за любыми ответами на эту запись через канал RSS 2.0.
Вы можете перейти к концу и оставить отзыв. Пинг в настоящее время не разрешен.

MySQL: вставить запись, если она не существует в таблице

  СОЗДАТЬ ТАБЛИЦУ `имя_таблицы` (
  `id` int (11) NOT NULL auto_increment,
  `name` varchar (255) НЕ NULL,
  `адрес` varchar (255) НЕ NULL,
  `tele` varchar (255) NOT NULL,
  ПЕРВИЧНЫЙ КЛЮЧ (`id`)
) ДВИГАТЕЛЬ = InnoDB;  

Вставить запись:

  ВСТАВИТЬ  имя_таблицы  (имя, адрес, теле)
ВЫБРАТЬ * ИЗ (ВЫБРАТЬ 'Назир', 'Калькутта', '033') КАК tmp
ГДЕ НЕ СУЩЕСТВУЕТ (
    ВЫБЕРИТЕ имя ИЗ имя_таблицы, ГДЕ имя = 'Назир'
) LIMIT 1;  

Запрос в порядке, затронута 1 строка (0. 00 сек)
Записей: 1 Дубликатов: 0 Предупреждений: 0

  ВЫБРАТЬ * ИЗ `имя_таблицы`;

+ ---- + -------- + ----------- + ------ +
| id | имя | адрес | теле |
+ ---- + -------- + ----------- + ------ +
| 1 | Назир | Калькутта | 033 |
+ ---- + -------- + ----------- + ------ +  

Попробуйте снова вставить ту же запись:

  ВСТАВИТЬ  имя_таблицы  (имя, адрес, теле)
ВЫБРАТЬ * ИЗ (ВЫБРАТЬ 'Назир', 'Калькутта', '033') КАК tmp
ГДЕ НЕ СУЩЕСТВУЕТ (
    ВЫБЕРИТЕ имя ИЗ имя_таблицы, ГДЕ имя = 'Назир'
) LIMIT 1;

Запрос выполнен, затронуты 0 строк (0.00 сек)
Записей: 0 Дубликатов: 0 Предупреждений: 0

+ ---- + -------- + ----------- + ------ +
| id | имя | адрес | теле |
+ ---- + -------- + ----------- + ------ +
| 1 | Назир | Калькутта | 033 |
+ ---- + -------- + ----------- + ------ +  

Вставить другую запись:

  ВСТАВИТЬ  имя_таблицы  (имя, адрес, теле)
ВЫБРАТЬ * ИЗ (ВЫБРАТЬ 'Сантош', 'Кестопур', '044') КАК tmp
ГДЕ НЕ СУЩЕСТВУЕТ (
    ВЫБЕРИТЕ имя ИЗ имя_таблицы, ГДЕ имя = 'Сантош'
) LIMIT 1;  

Запрос в порядке, затронута 1 строка (0. 00 сек)
Записей: 1 Дубликатов: 0 Предупреждений: 0

  ВЫБРАТЬ * ИЗ `имя_таблицы`;

+ ---- + -------- + ----------- + ------ +
| id | имя | адрес | теле |
+ ---- + -------- + ----------- + ------ +
| 1 | Назир | Калькутта | 033 |
| 2 | Сантош | Кестопур | 044 |
+ ---- + -------- + ----------- + ------ +  

Нравится:

Нравится Загрузка …

Связанные

MySQL существует 的 用法 介绍 — 简明 现代 魔法

2011 09 07 日 那天 写 的 已经 有 168188 阅读 了

君 一共 花费 了 167.717 мс 进行 了 5 次 数据库 查询 ， 努力 地 为 您 提供 了 这个 页面。

有 一个 查询 如下 ：

ВЫБЕРИТЕ c.CustomerId, CompanyName
ОТ клиентов c
ГДЕ СУЩЕСТВУЕТ (
ВЫБРАТЬ ИД ЗАКАЗА ИЗ О
ГДЕ o.CustomerID = cu.CustomerID)
 

里面 的 EXISTS 运作 子 返回 的 是 OrderId 字段 可是 的 查询 要找 的 是 CustomerID 和 CompanyName OrderID 如何 匹配

СУЩЕСТВУЕТ 用于 检查 子 查询 是否 至少 会 返回 一行 ， 该 子 查询 实际上 并不 返回 任何 ， 而是 返回 True 或 False。

EXISTS 一个 子 查询 ， 检测 的 存在。 语法 EXISTS subquery。 subquery 一个 受限 的 SELECT 语句 （不允许 有 COMPUTE 子句 和 INTO 关键字）。 Boolean ， 如果 子 包含则 返回 ИСТИНА。

• 子 查询 中 使用 NULL 仍然 返回 结果 集

例子 在 子 查询 中 指定 NULL ， 并 返回 结果 集 ， 通过 使用 EXISTS 仍 取值 为 TRUE。

ВЫБЕРИТЕ CategoryName
ИЗ категорий
ГДЕ СУЩЕСТВУЕТ (ВЫБРАТЬ NULL)
ЗАКАЗАТЬ ПО КАТЕГОРИИ ASC
 

例子 比较 语义 类似 的 查询。 第 一个 查询 使用 EXISTS 而 第二 个 IN。 注意 两个 返回 的 信息。

ВЫБЕРИТЕ DISTINCT pub_name
ОТ издателей
ГДЕ СУЩЕСТВУЕТ
    (ВЫБРАТЬ *
    ИЗ названий
    ГДЕ pub_id = издатели. pub_id
    И тип = 'бизнес')
 

ВЫБЕРИТЕ отдельное имя pub_name
ОТ издателей
ГДЕ pub_id IN
    (ВЫБЕРИТЕ pub_id
    ИЗ названий
    ГДЕ тип = 'бизнес')
 

本 示例 查找 子 在 城市 中 的 作者 的 两种 查询 方法 ： 一种 方法 使用 = ANY ， 第二种 方法 EXISTS。 两种 方法 返回。

ВЫБЕРИТЕ au_lname, au_fname
ОТ авторов
ГДЕ существует
    (ВЫБРАТЬ *
    ОТ издателей
    ГДЕ авторы.city = publishers.city)
 

ВЫБЕРИТЕ au_lname, au_fname
ОТ авторов
ГДЕ город = ЛЮБОЙ
    (ВЫБЕРИТЕ город
    ОТ издателей)
 

查询 位于 以 字母 B 的 城市 中 的 任 一 կ 的 书名

ВЫБЕРИТЕ заголовок
ИЗ названий
ГДЕ СУЩЕСТВУЕТ
    (ВЫБРАТЬ *
    ОТ издателей
    ГДЕ pub_id = названия.pub_id
    И город КАК 'B%')
 

ВЫБЕРИТЕ заголовок
ИЗ названий
ГДЕ pub_id IN
    (ВЫБЕРИТЕ pub_id
    ОТ издателей
    ГДЕ город КАК 'B%')
 

НЕ СУЩЕСТВУЕТ. НЕ СУЩЕСТВУЕТ. НЕ СУЩЕСТВУЕТ.

ВЫБЕРИТЕ pub_name
ОТ издателей
ГДЕ НЕ СУЩЕСТВУЕТ
    (ВЫБРАТЬ *
    ИЗ названий
    ГДЕ pub_id = publishers.pub_id
    И тип = 'бизнес')
ЗАКАЗАТЬ ПО pub_name
 

又 比如 以下 SQL 语句 ：

выбрать отличное 姓名 от xs
где не существует (
выберите * из kc
где не существует (
выберите * из xs_kc
где 学 号 = xs.学 号 и 课程 号 = kc. 课程 号
)
 

最 外层 的 查询 xs 里 的 数据 一行 一行 的 做 里 层 的 子 查询。

中间 的 exists 语句 只 对 上 一层 的 返回 true 或 false ， 因为 查询 的 条件 都 在, где 学 号 = xs. 学 号 и 课程 号 = kc. 课程 号 这 句话 里。 每值。 它 只是 告诉 一层 ， 最 外层 的 查询 条件 在 这里 成立 或 都不 成立 ， 的 时候 值 也 一样 回 返回 上去。 的 时候 如果 true （真） 就 到 结果。 为ложно （假） 丢弃。

где не существует
выберите * из xs_kc
где 学 号 = xs. 学 号 и 课程 号 = kc. 课程 号
 

这个 существует 就是 告诉 上 一层 ， 这一 行 语句 在 我 这里 不 成立。 因为 他 不是 高层 ， 所以 还要 继续 向上 返回。

выбрать отличное 姓名 из xs
где не существует （这里 的 上 一个 false 的 在 判断 结果 true （成立） 由于 是 高层 的 结果 （这里 指 是 查询 条件）返回 到 结果 集。

几个 重要 的 点 ：

• 里 层 要用 到 的 醒 询 条件 的 表 比如: xs.
  

  No related posts.