php mysql create table if not exists

Несколько заметок о MySQL

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

Буду использовать дефолтные настройки MySQL. Некоторые заметки связаны с PHP, поэтому для примеров буду использовать расширение mysqli.

Для запуска sql-запросов из статьи можно инициализировать таблицы так:

Проверить текущие автоинкременты можно так:

У обоих таблиц они равны 4. При этом имеется такое подключение к БД:

Вставка по уникальному ключу и автоинкремент

Если в таблице есть уникальный ключ, то для вставки и обновления есть три способа переложить проверку уникальности на MySQL: INSERT IGNORE, INSERT… ON DUPLICATE KEY UPDATE, REPLACE. Каждый тип запросов по разному ведет себя с автоинкрементом на разных типах таблиц:

Автоинкремент стал 5, хотя вставки не было. А что будет с MyISAM:

Там автоинкремент остался 4. Аналогичная ситуация будет с ON DUPLICATE KEY UPDATE:

В user_innodb автоинкремент стал 6, а у user_myisam остался 4.

REPLACE работает иначе: в случае нахождения совпадений в уникальном ключе, он удалит старую запись и добавт новую.

Увеличит автоинкремент до 7, теперь у Петрова >
У MyISAM аналогично:

Автоинкремент стал 5, а Петров получил >
Итак, REPLACE работает на обоих движках таблиц одинаково, а INSERT IGNORE и ON DUPLICATE KEY UPDATE изменяют автоинкремент на InnoDB.

Получение id изменяемой записи после обновления

Выведет 7 и 7, первый раз запись была добавлена под второй раз изменена.

С INSERT IGNORE такой трюк не выйдет. Код ниже выведет 9 и 0

Реализация SEQUENCE

У MySQL, в отличие от других СУРБД, нет такой штуки как SEQUENCE. Есть автоинкремент, но он не позвоялет решить все задачи, с которыми может помочь SEQUENCE. Например, шардинг.

В общем случае, если надо раскладывать записи по различным таблицам или даже базам, нужна будет мастер-таблица с автоинкрементным полем, в котором централизованно генерировался бы ID новой записи.

Решить эту задачу можно так:

В одной таблице получается сразу несколько последовательностей, в этом примере три.

Далее можно получать следующий ID из нужной последовательности с помощью функции
last_insert_id():

Для автоинкремента есть возможность указать шаг приращения опцией конфигурации auto_increment_increment. В этом примере такую функцию можно реализовать примерно так:

Немного о беззнаковых целых

С аккуратностью используйте беззнаковые целые как типы полей MySQL, если обращаетесь к БД из PHP.

История моего «озарения» по этому поводу. Для поля id всегда использовал беззнаковый целый тип, все равно классический id не бывает отрицательным. Однажды, генерируя модель с помощью Gii (скаффолдинг Yii), я обратил внимание, на то что правила валидации в модели для моих id и других беззнаковых целых полей генерируются как для строк. “WTF?” — подумал я и полез в код фреймворка, где обнаружил, что при разборе типов полей есть такой “хардкод” проверки на наличие unsigned:

Я посчитал это ошибкой, обрадовался, что сейчас у меня есть шанс внести свою лепту в исправление багов Yii. Но радость быстро сменилась мыслью “это ж-ж-ж-ж, неспроста”.

Действительно, в PHP нет беззнаковых целых, а в общем случае целые в PHP 32-х разрядные (под 32-bit Linux и под Windows). Если целочисленное значение превышает PHP_INT_MAX, то оно приводится к float, и тут самое место для возникновения магии со странными багами. Так что господин Qiang Xue все правильно сделал.

Источник

MySQL CREATE TABLE IF NOT EXISTS in PHPmyadmin import

I have the following code

this table already exists in the database, but when i import an sql file with phpmyadmin, the following error occurs

i thought because it already exists it won’t attempt to create it, why is it behaving as such?

5 Answers 5

On the CREATE TABLE,

The AUTO_INCREMENT of abuse_id is set to 2. MySQL now thinks 1 already exists.

With the INSERT statement you are trying to insert abuse_id with record 1. Please set AUTO_INCREMENT on CREATE_TABLE to 1 and try again.

Otherwise set the abuse_id in the INSERT statement to ‘NULL’.

How can i resolve this?

it is because you already defined the ‘abuse_id’ as auto increment, then there is no need to insert its value. it will be inserted automatically. the error comes because you are inserting 1 many times that is duplication of data. the primary key should be unique. should not be repeated.

the thing you have to do is to change your insertion query as below

Depending on what you want to accomplish, you might replace INSERT with INSERT IGNORE in your file. This will avoid generating an error for the rows that you are trying to insert and already exist.

If you really want to insert this record, remove the `abuse_id` field and the corresponding value from the INSERT statement :

Источник

Running this twice in the MySQL Query Browser results in:

Table ‘t1’ already exists Error 1050

I would have thought that creating the table «IF NOT EXISTS» would not throw errors. Am I missing something or is this a bug? I am running version 5.1. Thanks.

9 Answers 9

Works fine for me in 5.0.27

I just get a warning (not an error) that the table exists;

As already stated, it’s a warning not an error, but (if like me) you want things to run without warnings, you can disable that warning, then re-enable it again when you’re done.

You can use the following query to create a table to a particular database in MySql.

I have a solution to a problem that may also apply to you. My database was in a state where a DROP TABLE failed because it couldn’t find the table. but a CREATE TABLE also failed because MySQL thought the table existed. (This state could easily mess with your IF NOT EXISTS clause).

I eventually found this solution:

(Running on OS X 10.6)

Create mysql connection with following parameter. «‘raise_on_warnings’: False». It will ignore the warning. e.g.

I had a similar Problem as @CraigWalker on debian: My database was in a state where a DROP TABLE failed because it couldn’t find the table, but a CREATE TABLE also failed because MySQL thought the table still existed. So the broken table still existed somewhere although it wasn’t there when I looked in phpmyadmin.

I created this state by just copying the whole folder that contained a database with some MyISAM and some InnoDB tables

(this is not recommended!)

All InnoDB tables where not visible in the new database test in phpmyadmin.

sudo mysqladmin flush-tables didn’t help either.

My solution: I had to delete the new test database with drop database test and copy it with mysqldump instead:

Источник

Mysql create table if not exists

Оператор CREATE TABLE создает таблицу с заданным именем в текущей базе данных. Правила для допустимых имен таблицы приведены в разделе Раздел 6.1.2, «Имена баз данных, таблиц, столбцов, индексы псевдонимы». Если нет активной текущей базы данных или указанная таблица уже существует, то возникает ошибка выполнения команды.

В версии MySQL 3.23 и более поздних можно использовать ключевые слова IF NOT EXISTS для того, чтобы не возникала ошибка, если указанная таблица уже существует. Следует учитывать, что при этом не проверяется идентичность структур этих таблиц.

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

Каждая таблица tbl_name представлена определенными файлами в директории базы данных. В случае таблиц типа MyISAM это следующие файлы:

Чтобы получить более полную информацию о свойствах различных типов столбцов, Раздел 6.2, «Типы данных столбцов»:

Последнюю внесенную строку можно найти с помощью следующего запроса (чтобы сделать MySQL совместимым с некоторыми ODBC-приложениями):

CREATE TABLE автоматически принимает текущую открытую транзакцию в InnoDB если в MySQL включен двоичный журнал.

Величины NULL для столбца типа TIMESTAMP обрабатываются иначе, чем для столбцов других типов. В столбце TIMESTAMP нельзя хранить литерал NULL ; при установке данного столбца в NULL он будет установлен в текущее значение даты и времени. Поскольку столбцы TIMESTAMP ведут себя подобным образом, то атрибуты NULL и NOT NULL неприменимы в обычном режиме и игнорируются при их задании.

В MySQL ключ UNIQUE может иметь только различающиеся значения. При попытке добавить новую строку с ключом, совпадающим с существующей строкой, возникает ошибка выполнения команды.

Если ключ PRIMARY или UNIQUE состоит только из одного столбца и он принадлежит к числовому типу, то на него можно сослаться также как на _rowid (новшество версии 3.23.11).

При использовании выражений ORDER BY или GROUP BY со столбцом типа TEXT или BLOB используются только первые max_sort_length байтов. See Раздел 6.2.3.2, «Типы данных BLOB и TEXT ».

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

Максимальную длину записи в байтах можно вычислить следующим образом:

Опции table_options и SELECT реализованы только в версиях MySQL 3.23 и выше. Ниже представлены различные типы таблиц:

Это означает, что при наличии нескольких одинаковых ключей в двух строках записи все последующие «аналогичные» ключи будут занимать только по 2 байта (включая указатель строки). Сравним: в обычном случае для хранения последующих ключей требуется размер_хранения_ключа + размер_указателя (обычно 4) байтов. С другой стороны, если все ключи абсолютно разные, каждый ключ будет занимать на 1 байт больше, если данный ключ не может иметь величину NULL (в этом случае уплотненный ключ будет храниться в том же байте, который используется для указания, что ключ равен NULL ).

В значительной степени говорит само за себя, но по какой-то причине он всегда работает вместо того, чтобы работать один раз (когда таблица не существует) …

Решение

Вы должны проверить, если таблица существует или нет, потому что $ create_tbl вернуть 1 каждый раз.
так беги SHOW TABLES LIKE ‘student’ до

Другие решения

Вы проверяете, существует ли оператор SQL, а не результат. Тем не менее, вам необходимо строго проверить успешное выполнение оператора с помощью === (не просто == или тестирование переменной напрямую). Удалить IF NOT EXISTS из запроса, а затем проверить, как это;

Я никогда не работал с MySQLi API раньше (всегда использую PDO ), но разве он не возвращает истину, потому что запрос действительно был успешным?

Я думаю, что он возвращает false только если ваше соединение не удалось или такая ошибка.

Я только что удалил IF NOT EXISTS и это работает как шарм! Спасибо @Mark Baker за то, что указал на это! 🙂

I have the following code

this table already exists in the database, but when i import an sql file with phpmyadmin, the following error occurs

i thought because it already exists it won’t attempt to create it, why is it behaving as such?

5 Answers 5

On the CREATE TABLE,

The AUTO_INCREMENT of abuse_id is set to 2. MySQL now thinks 1 already exists.

With the INSERT statement you are trying to insert abuse_id with record 1. Please set AUTO_INCREMENT on CREATE_TABLE to 1 and try again.

Otherwise set the abuse_id in the INSERT statement to ‘NULL’.

How can i resolve this?

it is because you already defined the ‘abuse_id’ as auto increment, then there is no need to insert its value. it will be inserted automatically. the error comes because you are inserting 1 many times that is duplication of data. the primary key should be unique. should not be repeated.

the thing you have to do is to change your insertion query as below

Depending on what you want to accomplish, you might replace INSERT with INSERT IGNORE in your file. This will avoid generating an error for the rows that you are trying to insert and already exist.

If you really want to insert this record, remove the `abuse_id` field and the corresponding value from the INSERT statement :

In your case, the first value to insert must be NULL, because it’s AUTO_INCREMENT.

Источник

запуск этого дважды в браузере запросов MySQL приводит к:

таблица ‘ t1 ‘ уже существует ошибка 1050

Я бы подумал, что создание таблицы «если не существует» не приведет к ошибкам. Я что-то пропустил или это баг? Я запускаю версию 5.1. Спасибо.

7 ответов

отлично работает для меня в 5.0.27

Я просто получаю предупреждение (а не ошибку) о том, что таблица существует;

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

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

у меня есть решение проблемы, которая может также относиться к вам. Моя база данных была в состоянии, когда DROP TABLE не удалось, потому что он не мог найти стол. но CREATE TABLE также не удалось, потому что MySQL думал, что таблица существует. (Это состояние может легко испортить ваше предложение IF NOT EXISTS).

в конце концов я нашел данное решение:

(работает на OS X 10.6)

у меня была похожая проблема, как @CraigWalker на debian: моя база данных была в состоянии, когда DROP TABLE не удалось, потому что он не мог найти таблицу, но CREATE TABLE также не удалось, потому что MySQL думал, что таблица все еще существует. Таким образом, сломанный стол все еще существовал где-то, хотя его не было, когда я смотрел в phpmyadmin.

я создал это состояние, просто скопировав всю папку, содержащую базу данных с некоторыми MyISAM и InnoDB таблицы

(эта не рекомендуется!)

все InnoDB таблицы, где не видно в новой базе данных test в phpmyadmin.

sudo mysqladmin flush-tables не помогло.

мое решение: мне пришлось удалить новую тестовую базу данных с помощью drop database test и скопируйте его с mysqldump вместо:

Источник