In this article, we will discuss why Error 1264 occurs and how to resolve it.
Table of Contents
- Introduction
- Error code 1264. out of range value for column at row 1
- Error code 1264. out of range value for column decimal
Introduction
MySQL server throws the error 1264 if the MySQL query or statement stores a value in a numeric column outside the permissible range of the column data type. This error will occur if strict SQL mode is enabled; otherwise, the MySQL server will clip the value to an appropriate endpoint value of the column data type range.
Let us look into a few examples to have some more clarity. We will get started by making a sample table student_enroll_data.
#create the table CREATE TABLE student_enroll_data ( student_id INT, student_name VARCHAR(50), enroll_date DATE, student_ssn_no INT, fee_submitted DECIMAL(10,2) ); SELECT * FROM student_enroll_data;
Action Output Message Response:-
Advertisements
0 row(s) returned.
Output:-
The table student_enroll_data is empty at the moment. Let us try to insert some rows.
INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) VALUES(1,"Daniel",'2021-12-12',3147483647,12378.90), (2,"Sandy",'2021-10-12',1147483788,14578.90), (3,"Ryma",'2021-11-22',1147483789,22378.90), (4,"Jennifer",'2021-12-02',1147483790,12378.90), (5,"Austin",'2021-11-12',1147483791,12378.90), (6,"George",'2021-10-10',1147483792,12788.90), (7,"Veronica",'2021-02-13',1147483793,12378.90);
Action Output Message Response:-
Error Code: 1264. Out of range value for column ‘student_ssn_no’ at row 1.
We got the error 1264 because we are trying to save the value 3147483647, which is out of range for INT in the first row.
If we notice the datatype of the student_ssn_no is INT, and the error occurred because the value of this column in row 1 is more than the maximum value of INT. Here we are trying to save value 3147483647, which is more than the maximum INT value: 2147483647.
To fix the issue, we will have to store a less than 2147483647 or change the data type of student_ssn_no to BIGINT. Let us modify the column to BIGINT and try inserting the data again.
#Alter the table, modify the column student_ssn_no ALTER TABLE student_enroll_data MODIFY student_ssn_no BIGINT ; #insert rows into the table INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) VALUES(1,"Daniel",'2021-12-12',3147483647,12378.90), (2,"Sandy",'2021-10-12',1147483788,14578.90), (3,"Ryma",'2021-11-22',1147483789,22378.90), (4,"Jennifer",'2021-12-02',1147483790,12378.90), (5,"Austin",'2021-11-12',1147483791,12378.90), (6,"George",'2021-10-10',1147483792,12788.90), (7,"Veronica",'2021-02-13',1147483793,12378.90);
Let us confirm what got inserted by executing:
SELECT * FROM student_enroll_data;
Output:-
As we can see in the above image, student_ssn_no for row 1 got inserted without any error now.
Error code 1264. out of range value for column decimal
This section will see why the error code 1264 occurs for the columns with the decimal data type and the solution.
Again let us try to insert one more row into the table student_enroll_data.
INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) VALUES(8,"Lorem ipsum",'2021-08-12',1147483794, 111111111.12);
Action Output Message Response:-
Error Code: 1264. Out of range value for column ‘fee_submitted’ at row 1
Instead of the row getting inserted, we got the error 1264 for the value 111111111.12 going out of range for the column fee_submitted. If we notice the datatype of the fee_submitted is DECIMAL(10,2).
DECIMAL(10,2) means that 10 digits can be allowed, including the two decimal places. Here, we are trying to insert a value that has 11 digits. Therefore only the values with a maximum of 8 digits before the decimal will be allowed. Let us change the value of the column fee_submitted to 99999999.12 and observe the difference.
INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) VALUES(8,"Lorem ipsum",'2021-08-12',1147483794, 99999999.12);
Action Output Message Response:-
1 row(s) affected.
SELECT * FROM student_enroll_data;
Output:-
As we can see in figure 3, the row is inserted successfully.
Another solution is to modify the data type of the column fee_submitted to DECIMAL(11,2) which will allow 11 digits. We will change the datatype of fee_submitted and try inserting the row again.
#modify the datatype of column fee_submitted ALTER TABLE student_enroll_data MODIFY fee_submitted DECIMAL(11,2) ; #Insert the row INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) VALUES(9,"Lorem ipsum",'2021-08-12',1147483794, 123456789.12);
Action Output Message Response:-
1 row(s) affected.
SELECT * FROM student_enroll_data WHERE student_id = 9;
Output:-
As shown in figure 4, the value 123456789.12 is inserted successfully.
We hope this article helped you understand and resolve Error 1264 out of range value for a column. Good Luck!!!
Advertisements
Thanks for reading.
Out of range value for column: In this article we will discuss why Error code 1264 in MySQl occurs and how we can resolve it.
Overview :
MYSQL out of range value for column: This error arises mainly when strict SQL is enabled and the MySQL query stores a value in numeric column outside permissible range of column data type. If strict SQL is not enabled then MySQL server will clip the value appropriate to column data type range.
# to create the table CREATE TABLE studentEnroll_data ( student_roll INT, stud_name VARCHAR(50), date_of_adm DATE, student_sub_no INT, fee_paid DECIMAL(8,2) ); SELECT * FROM studentEnroll_data;
Output :
Error code 1264. out of range value for column at row 1
As studentEnroll_data is empty let’s insert some values to it. If we will try to save a value larger than maximum value of 1264 then will get the error saying-
For example: Write this in code – #1264 – Out Of Range Value For Column
Error Code: 1264. Out of range value for column ‘student_sub_no’ at row 1.
Likewise: Out Of Range Value For Column ‘Phone’ At Row 1.
We can remove the issue by storing a value less than maximum value of INT or change the data type of student_sub_no to BIGINT.
#Alter the table, modify the column student_sub_no ALTER TABLE studentEnroll_data MODIFY student_ssn_no BIGINT ; #insert rows into the table INSERT INTO studentEnroll_data(student_roll,stud_name,date_of_adm,student_sub_no,fee_paid) VALUES(1,"Rohit",'1999-06-22',321458795,12000.50), (2,"Sourav",'1999-11-02',1447480374,12500.50), (3,"Tarun",'1999-01-22',0197485789,32478.50), (4,"Soumya",'1999-03-25',1145484791,25874.50), (5,"Meghna",'1999-04-29',0144483594,12378.50), (6,"Aditya",'1999-12-25',0142443794,25000.50), (7,"Sahil",'1999-05-14',1447443791,32178.50); SELECT * FROM studentEnroll_data;
Output :
Error code 1264. out of range value for column decimal :
Let’s try to add one more row with value in fee_paid column as 125487495.50. But fee_paid column can only allow 10 digits including two decimal places. So we will get a error message as-
Error Code: 1264. Out of range value for column ‘fee_paid’ at row 1
To avoid this we can try inserting one more row by modifing the data type of column fee_paid to DECIMAL(10,2). So now columns can accept total 12 digits.
#change the datatype of fee_submitted column ALTER TABLE student_enroll_data MODIFY fee_paid DECIMAL(10,2) ; #Inserting a row INSERT INTO studentEnroll_data(student_roll,student_name,date_of_adm,student_sub_no,fee_paid) VALUES(8,"Suresh",'1999-09-12',1267483794, 1958295862.12); SELECT * FROM studentEnroll_data WHERE student_roll = 9;
Output
You can also read for more perfection in this topic.
- Out Of Range Value For Column ‘Contact_No’ At Row 1
- Numeric Value Out Of Range: 1264 Out Of Range Value For Column
- Out Of Range Value For Column Decimal At Row 1
- Sql Error 1264
- Mysql Decimal Out Of Range
- 1264 Out Of Range Value For Column
- Error 1264 In Mysql
- Out of range value mysql
- Out Of Range Value For Column Sql
The error, ‘mysql_error out of range value for column’ appears in the /var/log/messages file on the Nagios XI server.
As part of our Server Management Services, we assist our customers with several Nagios queries.
Today, let us see how to effectively resolve this error.
mysql_error out of range value for column
The error in the /var/log/messages file on the Nagios XI server looks like this:
Apr 23 23:30:03 centosxx ndo2db: Error: mysql_query() failed for ‘INSERT INTO nagios_scheduleddowntime SET instance_id=’1′, downtime_type=’1′, object_id=’830′, entry_time=FROM_UNIXTIME(1517155261), author_name=’nagios_user’, comment_data=’AUTO: Standby Server’, internal_downtime_id=’804′, triggered_by_id=’0′, is_fixed=’1′, duration=’31536000′, scheduled_start_time=FROM_UNIXTIME(1517239800), scheduled_end_time=FROM_UNIXTIME(1548775800) ON DUPLICATE KEY UPDATE instance_id=’1′, downtime_type=’1′, object_id=’830′, entry_time=FROM_UNIXTIME(1517155261), author_name=’nagios_user’, comment_data=’AUTO: Standby Server’, internal_downtime_id=’804′, triggered_by_id=’0′, is_fixed=’1′, duration=’31536000′, scheduled_start_time=FROM_UNIXTIME(1517239800), scheduled_end_time=FROM_UNIXTIME(1548775800)’ Apr 23 23:30:03 centosxx ndo2db: mysql_error: ‘Out of range value for column ‘duration’ at row 1′
The error above may not be identical. However, it usually follows the ‘Out of range value for column message’.
It is the result of the MySQL/MariaDB server having the SQL Mode set to STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION.
Generally, this problem arises when we attempt to offload the databases to an external server using a MySQL/MariaDB custom installation.
With the following steps, we can identify what the SQL Mode is currently configured for.
Initially, we establish a terminal session to MySQL/MariaDB server hosting the Nagios XI databases.
Then we execute the following command:
mysql -u root -p’nagiosxi’ -e “SELECT @@GLOBAL.sql_mode;”Replace the password of Nagios xi with the password of the root user on the database server.
The output shows us the SQL Mode has been defined:
+——————————————–+ | @@GLOBAL.sql_mode | +——————————————–+ | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +——————————————–+ Output that shows that NO SQL Mode has been defined: +——————-+ | @@GLOBAL.sql_mode | +——————-+ | | +——————-+
Solution
To resolve this issue, we need to define the SQL Mode in the MySQL/MariaDB my.cnf configuration file.
Initially, we have to stop the required services on the Nagios XI server:
RHEL 7/8|CentOS 7/8|Debian|Ubuntu 16/18
systemctl stop nagios.service
systemctl stop ndo2db.serviceThen we have to edit the my.cnf configuration file on MySQL/MariaDB database server.
- RHEL 8|CentOS 8
vi /etc/my.cnf.d/mysql-server.cnf
- RHEL 6/7| CentOS 6/7
vi /etc/my.cnf
- Ubuntu 16/18
vi /etc/mysql/mysql.conf.d/mysqld.cnf
- Debian 9
vi /etc/mysql/mariadb.conf.d/50-server.cnf
Eventually, locate the [mysqld] section and check to see if there exists a sql_mode:
[mysqld]
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESIf it does, we need to replace it with the following. If it does not exist, we need to add the following:
[mysqld] sql_mode=””
Then we save and close the file.
Later, we need to restart the database service:
- RHEL 7|CentOS 7|Debian 9
systemctl restart mariadb.service- Debian 8|Ubuntu 16/18
systemctl restart mysql.serviceOnce done, execute the following command to ensure the SQL Mode is no longer set:
mysql -u root -p’nagiosxi’ -e “SELECT @@GLOBAL.sql_mode;”Replace the password of Nagios xi with that of the root user on the database server.
A successful output with NO SQL Mode looks like this:
+——————-+ | @@GLOBAL.sql_mode | +——————-+ | | +——————-+
If it is correct, we can start the services on Nagios XI:
RHEL 7|CentOS 7|Debian|Ubuntu 16/18
systemctl start ndo2db.service
systemctl start nagios.serviceThen we check the /var/log/messages file to ensure the error messages no longer appear.
[Failed with the resolution? We are here for you]
Conclusion
In short, the error appears in the /var/log/messages file on the Nagios XI server. Today, we saw how our Support Techs fix it
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
Очень часто в интернете встречаюсь со статьями, в которых приводят кучу примеров с якобы странным поведением MySQL по сравнению с другими БД. Чтобы стало понятно, о чём я говорю, приведу несколько примеров:
1. Деление на ноль возвращает NULL вместо ошибки
2. Выход за диапазон допустимых значений неявно приводит число к допустимому значению, а не к ошибке и откату транзакции
3. Вставка неверного типа данных также приводит к неявному преобразованию и успешному выполнению операции
Таких примеров я могу привести огромное число, но цель статьи не сделать очередное собрание высосанных из пальца примеров, а объяснить, почему происходит то или иное действие. Вся эта мистика MySQL давно описана в документации и легко объяснима, в чём вы сможете убедиться сами, прочитав статью до конца.
Для меня эта первая статья на хабре, поэтому я старался писать дотошно подробно. Уверен, что она будет полезна всем, кто работает с MySQL. Большую помощь в написании статьи оказала подготовка к сдаче на сертификат разработчика MySQL, а точнее книга «MySQL Certification Study Guide».
Итак, мой друг, начнём!
SQL Modes
SQL modes – это настройка поведения работы сервера MySQL, состоящая из режимов, каждый из которых контролирует какой-либо один аспект обработки запроса.
Возможности SQL mode:
1. Устанавливает строгую или мягкую проверку входных данных
2. Включает или отключает следование SQL стандарту
3. Обеспечивает лучшую синтаксическую совместимость с другими БД
По сути, SQL mode очень мощный механизм тюнинга БД, позволяющий гибко манипулировать обработкой запросов и уведомлениями MySQL.
Прежде чем мы перейдём к последующей теории, вы должны строго-настрого уяснить, что изменение режима SQL mode после создания и вставки данных в партиционные таблицы (partitioning tables) может привести к существенным изменениям в поведении таких таблиц, что, в свою очередь, может привести к потере или повреждению данных. Настоятельно рекомендуется, чтобы вы никогда не изменяли SQL режим после создания партиционных таблиц.
При репликации партиционных таблиц, отличающиеся параметры SQL mode на Primary и Slave MySQL серверах также может привести к проблемам. Для стабильной работы репликации между серверами, настройки SQL mode должны быть идентичными.
Теперь, после того, как вы осознали всю ответственность в использовании SQL режимов, перейдём к его сути.
Контроль текущего SQL режима происходит через системную переменную sql_mode. Для задания значения используется команда SET. Ниже представлены возможные варианты установки данного режима.
1. Соответствует значению по умолчанию для только что установленной БД (никаких специальных режимов не установлено). Кавычки являются обязательными.
SET sql_mode = '';
2. Установка одного режима sql_mode. Возможно два варианта – с кавычками и без них.
SET sql_mode = ANSI_QUOTES;
SET sql_mode = 'TRADITIONAL';
3. Установка нескольких режимов sql_mode. Указание кавычек является обязательным!
SET sql_mode = 'IGNORE_SPACE,ANSI_QUOTES';
SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVIZION_BY_ZERO'
Несмотря на то, что названия режимов регистронезависимые, я для удобства прочтения буду везде в статье писать их в верхнем регистре.
В примерах выше мы устанавливали режимы для текущей сессии, но если вы обладаете привилегиями суперпользователя, то можно задать глобальный режим для всего сервера и всех текущих коннектов, указав параметр GLOBAL. Полный синтаксис установки sql_mode выглядит так:
SET [GLOBAL|SESSION] sql_mode='параметры';
Для просмотра текущих значений глобального и сессионного режима сервера используйте следующие запросы:
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;
Помимо команды SET существует ещё два способа задания режима работы БД:
1. Запустить сервер с опцией —sql-mode=»<режимы>»
2. Установить в файле my.cnf (для unix подобных систем) или my.ini (для windows) параметр sql-mode=»<режимы>»
Краткий справочник режимов
ANSI_QUOTES
Заставляет сервер интерпретировать двойную кавычку ( » ) точно также, как и обратную кавычку ( ` ), при этом она теряет способность обрамлять строки. Как можно было догадаться, этот режим заставляет MySQL приблизиться к SQL стандарту.
mysql> CREATE TABLE test11 (`order` INT NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.28 sec)
mysql> CREATE TABLE test12 ("order" INT NULL) ENGINE = InnoDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"order" INT NOT NULL) ENGINE = InnoDB' at line 1
mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE test12 ("order" INT NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)
IGNORE_SPACE
По умолчанию, между функцией и открывающейся круглой скобкой нельзя устанавливать пробелы. Включение этого режима разрешает серверу игнорировать пробелы, но платой за такую вольность станет то, что все функции станут зарезервированными словами, а значит, при совпадении имени столбца с именем функции придётся в обязательном порядке экранировать такой столбец.
mysql> SELECT COUNT (*) FROM test12;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM test12' at line 1
mysql> SET sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT (*) FROM test12;
+-----------+
| COUNT (*) |
+-----------+
| 0 |
+-----------+
1 row in set (0.01 sec)
ERROR_FOR_DIVISION_BY_ZERO
При делении на ноль в строгом режиме генерируется ошибка, а нестрогом — предупреждение при выполнении операторов INSERT или UPDATE. Без этого параметра деление на ноль возвращает предупреждение и вставляет в таблицу NULL. Про строгость будет сказано в следующем режиме, пока постарайтесь абстрагироваться.
mysql> SELECT 1 / 0;
+-------+
| 1 / 0 |
+-------+
| NULL |
+-------+
1 row in set (0.00 sec)
mysql> SHOW WARNINGS;
Empty set (0.00 sec)
mysql> INSERT INTO test12 VALUES (1/0);
Query OK, 1 row affected (0.02 sec)
mysql> SHOW WARNINGS;
Empty set (0.00 sec)
mysql> SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT 1 / 0;
+-------+
| 1 / 0 |
+-------+
| NULL |
+-------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------+
| Level | Code | Message |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)
mysql> INSERT INTO test12 VALUES (1/0);
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------+
| Level | Code | Message |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)
mysql> INSERT INTO test12 VALUES ('some string'/0);
Query OK, 1 row affected, 2 warnings (0.02 sec)
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'some string' |
| Warning | 1365 | Division by 0 |
+---------+------+-------------------------------------------------+
2 rows in set (0.00 sec)
В приведённых примерах мы получали исключительно предупреждения, потому что строгий режим был выключен. Понимание строгости очень важное понятие для БД MySQL, потому что в классических базах данных такого нет. Забегая вперёд, скажу, что все БД изначально строгие и не позволяют тех вольностей, которые есть в MySQL. Мягкость MySQL сложилась исторически, когда ещё не было InnoDB. Посудите сами, в нетранзакционных таблицах действуют совершенно другие правила, нежели чем в транзакционных, поэтому следование жестким правилам зачастую приводило бы к нежелательному результату.
STRICT_TRANS_TABLES
Включает «строгий режим» для всех таблиц, поддерживающих транзакции, т.е. на InnoDB и BDB. Этот режим возвращает ошибку, вместо предупреждения в следующих случаях:
1. Тип входных данных не соответствует заданному типу. Например, вставка строки в колонку c числовым типом
2. Число или дата находится вне допустимого диапазона. Диапазон определяется типом данных. Например, для типа unsigned tinyint допустимым диапазоном являются числа от 0 до 255
3. При вставке данных пропущено значение колонки, для которой не задано значение по умолчанию и имеет атрибут NOT NULL
4. Длина значения выходит за пределы заданного диапазона. Например, для колонки типа CHAR(5) вы не сможете вставить строку более 5 символов
5. Для типов ENUM и SET отсутствует вставляемое или обновляемое значение
Более подробно об особенностях работы данного режима будет рассказано отдельно в последующей ниже главе.
STRICT_ALL_TABLES
STRICT_ALL_TABLES полностью идентично STRICT_TRANS_TABLES, но действие режима уже распространяется на все таблицы MySQL, а не только на транзакционные.
Из-за разницы подходов к работе транзакционных и не транзакционных таблиц не всегда есть смысл использовать данный режим. Если это вам ещё не очевидно, то в главах о строгом и нестрогом режимах вы поймёте разницу.
TRADITIONAL
Композитный режим, включает в себя целый набор режимов, в который входит «строгий режим», а также ряд других режимов, налагающих ограничения на входные данные.
Заставляет MySQL вести себя как большинство «традиционных» баз данных SQL.
Посмотрим на полный перечень режимов, который содержит в себе данный режим.
mysql> SET sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@sql_modeG
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
ANSI
Другой композитный режим, делающий MySQL «ANSI-подобным», т.е. приближенным к стандарту SQL.
Включает в себя следующие режимы: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.
Последние два режима были обсуждены ранее, поэтому кратко опишу первые два:
REAL_AS_FLOAT – тип данных real является синонимом float, а не double.
PIPES_AS_CONCAT – разрешает использовать для конкатенации строк ( || ), вместо логического ИЛИ.
ONLY_FULL_GROUP_BY
Генерирует ошибку в запросах, в которых GROUP BY имеет не полный список не агрегированных параметров из SELECT и HAVING.
mysql> SELECT name, address, MAX(age) FROM test GROUP BY name;
ERROR 1055 (42000): 't.address' isn't in GROUP BY
mysql> SELECT name, MAX(age) as max_age FROM test GROUP BY name HAVING max_age < 30;
Empty set (0.00 sec)
ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause
Если вы желаете узнать обо всех SQL mode режимах и окунуться глубже в проблему, то милости прошу в официальную документацию http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html
Работа с SQL mode в PHP
По правде сказать, данную главу вряд ли можно назвать прикладной, потому что в реальных проектах конфигурировать нужно непосредственно на сервере MySQL, а не средствами языка программирования, поэтому глава скорее теоретическая, но для общего развития неплохо иметь ввиду и такой способ.
Чаще всего соединение с БД происходит через экземпляр класса PDO, поэтому рассмотрим его в деталях.
Есть два способа передать в БД специальные инструкции. Первый способ – передача в конструкторе. Посмотрим на полное описание конструктора:
PDO::__construct() ( string $dsn [, string $username [, string $password [, array $driver_options ]]] )
Думаю, с первыми тремя параметрами всё и так понятно, а вот $driver_options как раз таки предоставляет нам возможность выполнять запросы непосредственно в БД. Для задания системной переменной sql_mode режимом TRADITIONAL, будем делать так:
$db = new PDO('mysql:host=myhost;dbname=mydb', 'login', 'password', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode = 'TRADITIONAL'"));
Второй способ – конфигурирование на лету, через метод setAttribute;
$db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET sql_mode = 'TRADITIONAL'");
Конечно, некоторые из вас могут возразить, что можно выполнять запросы методом query или exec, но поскольку изначально глава теоретическая, то не буду заострять внимание на таком способе.
Дополнительно о PDO можно прочитать в официальной документации php.net/manual/ru/book.pdo.php
Предопределённые PDO константы для работы с MySQL php.net/manual/ru/ref.pdo-mysql.php
Строгий режим
Мы уже немного познакомились со строгим режимом в главе SQL Mode, когда изучали режимы STRICT_TRANS_TABLES, STRICT_ALL_TABLES и композитный TRADITIONAL. Уже из самого названия легко догадаться, что все входные данные проверяются с особой тщательностью и в случае нарушений любых ограничений, вас неминуемо будет ждать ошибка.
Ошибка в транзакционных таблицах вызывает откат транзакции (rollback). Даже если ваши запросы не предварены командой start transaction, то неявно каждый запрос в отдельности по-любому будет обёрнут командами start transaction и commit. Так работают все традиционные БД, что в равной степени относится и к транзакционным таблицам MySQL. Из этого следует, что нарушив ограничение, вызывается rollback, который откатывает все изменения.
Для не транзакционных таблиц всё немного сложнее. Так, при вставке, обновлении или удалении нескольких строк, в случае ошибки отменяется только последнее действие, вместо полного отката. Проиллюстрирую это на примере.
mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE test20 (
-> x TINYINT NOT NULL,
-> y TINYINT NOT NULL
-> ) ENGINE = MyISAM;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO test20 VALUES (1, 1), (2, 2), (3, 3), (NULL, 4), (5, 5);
ERROR 1048 (23000): Column 'x' cannot be null
mysql> SELECT * FROM test20;
+---+---+
| x | y |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
3 rows in set (0.01 sec)
mysql> UPDATE test20 SET x = x + 125, y = y + 1;
ERROR 1264 (22003): Out of range value for column 'x' at row 3
mysql> SELECT * FROM test20;
+-----+---+
| x | y |
+-----+---+
| 126 | 2 |
| 127 | 3 |
| 3 | 3 |
+-----+---+
3 rows in set (0.00 sec)
Генерация ошибки происходит в следующих случаях:
1. Тип вставляемых данных отличается от заданного типа столбца
2. Опущено значение для столбца, которому не задано значение по умолчанию и имеет атрибут NOT NULL
3. Для чисел и дат – данные находятся вне диапазона допустимых значений
4. Для строк – превышение допустимой длины
5. Для типов ENUM и SET – значение не является допустимым для заданного перечисления
6. Для столбца, определённого как NOT NULL — вставка NULL
Дефолтные значения для типов данных
Если в insert запросе не указаны данные для одной из колонок, то MySQL будет обрабатывать эту ситуацию в следующем порядке:
1. Если столбец имеет значение по умолчанию, то используется это значение и на этом всё заканчивается, в противном случае происходит переход к следующему шагу
2. Если столбец не имеет параметр NOT NULL, то присваивается NULL и на этом всё заканчивается, в противном случае поведение зависит от переменной sql_mode, точнее от строгости самого режима.
Как можно было догадаться из предыдущей главы, строгий режим сразу вернёт ошибку, которая в свою очередь откатит транзакцию для транзакционных таблиц или отменит последнее действие для не транзакционных таблиц.
Для нестрого режима будет вставлено значение по умолчанию для заданного типа данных.
Значения по умолчанию для каждого типа данных:
- Для типов int и float значение по умолчанию ноль (0)
- Для всех строк, кроме типа enum – пустая строка (»)
- Для enum – первый нумерованный член
- Для даты и времени – нулевые значения ‘0000-00-00 00:00:00’
- Для timestamp — текущая дата и время, если колонка была автоматически инициализирована, либо нулевыми значениями ‘0000-00-00 00:00:00’
Нестрогий режим
Ура! Наконец-то, мы добрались до самой «загадочной» части статьи, которую некоторые освещают как некую магию MySQL, но, увы, это лишь фокусы на потеху детей. И так, поехали!
Возможно, нужно было ранее описать все случаи, для которых применяются правила проверки данных, но я решил сделать это только сейчас. Их всего три, но каждый из них требует отдельного рассмотрения.
Запросы на модификация данных: INSERT, UPDATE, REPLACE, LOAD DATA INFILE
Обновление описания схем таблиц: ALTER TABLE
Задание значения по умолчанию (DEFAULT) в описании колонки
Напоминаю, что в строгом режиме некорректные данные приведут к генерации ошибки и откат данных, а в нестрогом – значение будет не явно приведено к корректному значению и сгенерировано предупреждение. Для просмотра ошибок используйте SHOW WARNINGS.
Ниже будут подробно рассмотрены все случаи обработки некорректных значений и их разрешений на уровне БД.
Выход из диапазона допустимых значений
Если число меньше минимального значения допустимого диапазона, то присваивается минимально-допустимое число. Если больше максимального – максимально-допустимое.
mysql> CREATE TABLE test31 (
-> i TINYINT NOT NULL,
-> j TINYINT NOT NULL,
-> k TINYINT NOT NULL
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO test31 (i, j, k) VALUES (-500, 10, 500);
Query OK, 1 row affected, 2 warnings (0.06 sec)
mysql> SELECT * FROM test30;
+------+----+-----+
| i | j | k |
+------+----+-----+
| -128 | 10 | 127 |
+------+----+-----+
1 row in set (0.00 sec)
Обработка строк
Строки длиннее заданной длины — усекаются.
mysql> CREATE TABLE test32 ( col1 VARCHAR(10) ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO test32 VALUES ('Im really the biggest string');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col1' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test32;
+------------+
| col1 |
+------------+
| Im really |
+------------+
1 row in set (0.00 sec)
ENUM и SET типы данных
Если присваиваемое значение колонке с типом ENUM не перечислено в определении ENUM, то MySQL преобразует его в пустую строку.
Если значение, которое присваивается SET столбцу содержит элементы, которые не перечислены в определении SET, то MySQL отбрасывает эти элементы, сохраняя значения только легальным элементам.
mysql> CREATE TABLE test33 (
-> col1 ENUM('One', 'Two', 'Three'),
-> col2 SET('One', 'Two', 'Three')
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO test33 VALUES ('Five', 'One,Three,Five');
Query OK, 1 row affected, 2 warnings (0.04 sec)
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col1' at row 1 |
| Warning | 1265 | Data truncated for column 'col2' at row 1 |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM test33;
+------+-----------+
| col1 | col2 |
+------+-----------+
| | One,Three |
+------+-----------+
2 rows in set (0.00 sec)
Преобразование в тип даты
При попытке сохранения значения, которое не может быть преобразовано в тип данных столбца, MySQL неявно преобразует его в значение по умолчанию для данного типа.
mysql> CREATE TABLE test34 (
-> col1 DATETIME,
-> col2 DATETIME
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.14 sec)
mysql> INSERT INTO test34 VALUES ('string', 123);
Query OK, 1 row affected, 1 warning (0.12 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'col1' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test34;
+---------------------+---------------------+
| col1 | col2 |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 2000-01-23 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
Таблица преобразований
| STRING | DATE | INT |
| ‘2010-03-12’ | ‘2010-03-12’ | 2010 |
| ’03-12-2010′ | ‘0000-00-00’ | 3 |
| ‘0017’ | ‘0000-00-00’ | 17 |
| ‘500 hats’ | ‘0000-00-00’ | 500 |
| ‘bartholomew’ | ‘0000-00-00’ | 0 |
Присвоение NULL для колонки с NOT NULL
Результат зависит от того, будет вставлена одна строка или множество в INSERT запросе.
При вставке одной строки возникает ошибка и изменения не применяются. При множественной вставке — MySQL неявно преобразует значение по умолчанию для этого типа данных.
Обновление описания схем таблиц: ALTER TABLE
При изменении типа данных на колонку налагаются ограничения нового типа, что может привести к неожиданному изменению самих данных по правилам описанных выше.
Если на колонку налагается ограничение NOT NULL, то все NULL значения конвертируются в значения по умолчанию для заданного типа данных текущей колонки. Значения по умолчанию описаны в главе «Дефолтные значения для типов данных»
mysql> CREATE TABLE test35 (
-> i INT NULL,
-> i2 INT NOT NULL,
-> j VARCHAR(20) NOT NULL,
-> k DATETIME
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.15 sec)
mysql> INSERT INTO test35 VALUES (NULL, 9999, 'very cool string', '1910-01-01 12:10:00');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM test35;
+------+------+------------------+---------------------+
| i | i2 | j | k |
+------+------+------------------+---------------------+
| NULL | 9999 | very cool string | 1910-01-01 12:10:00 |
+------+------+------------------+---------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE test35
-> CHANGE COLUMN `i` `i` INT(11) NOT NULL,
-> CHANGE COLUMN `i2` `i2` TINYINT(1) NOT NULL,
-> CHANGE COLUMN `j` `j` VARCHAR(2) NOT NULL,
-> CHANGE `k` `k` TIMESTAMP NULL DEFAULT NULL;
Query OK, 1 row affected, 4 warnings (0.42 sec)
Records: 1 Duplicates: 0 Warnings: 4
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'i' at row 1 |
| Warning | 1264 | Out of range value for column 'i2' at row 1 |
| Warning | 1265 | Data truncated for column 'j' at row 1 |
| Warning | 1264 | Out of range value for column 'k' at row 1 |
+---------+------+---------------------------------------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM test35;
+---+-----+----+---------------------+
| i | i2 | j | k |
+---+-----+----+---------------------+
| 0 | 127 | ve | 0000-00-00 00:00:00 |
+---+-----+----+---------------------+
1 row in set (0.00 sec)
Задание значения по умолчанию (DEFAULT) в описании колонки
Вообще, всё уже было сказано в прошлой главе, поэтому здесь нечего добавить.
Ну что ж, мой дорогой читатель. Теперь ты можешь по-праву назваться настоящим джедаем и получить чёрный пояс)))
Извлекаем плюсы
IGNORE
Ключевое слово IGNORE заставляет MySQL включать для такого запроса нестрогий режим. Также его можно использовать для генерации предупреждения вместо ошибки, при нарушении целостности первичного ключа (PRIMARY KEY) или уникальности (UNIQUE).
mysql> CREATE TABLE test40 (
-> x INT NOT NULL PRIMARY KEY
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.14 sec)
mysql> INSERT IGNORE INTO test40 VALUES (1), (2), (2), (3), (4);
Query OK, 4 rows affected (0.02 sec)
Records: 5 Duplicates: 1 Warnings: 0
mysql> SELECT * FROM test40;
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.02 sec)
mysql> INSERT INTO test40 VALUES (1), (2), (2), (3), (4);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> UPDATE IGNORE test40 SET x = 3 WHERE x = 2;
Query OK, 0 rows affected (0.07 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> UPDATE test40 SET x = 3 WHERE x = 2;
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
ON DUPLICATE KEY UPDATE
Такой вид запроса не совсем идеальный пример, но включил, чтобы лишний раз вспомнить, что такое вообще существует. Используется для выполнения вставки данных, либо обновлении при нарушении ограничения целостности первичного ключа (PRIMARY KEY) или уникальности (UNIQUE).
mysql> INSERT INTO test40 VALUES (10) ON DUPLICATE KEY UPDATE x = x + 10;
Query OK, 1 row affected (0.25 sec)
mysql> SELECT * FROM test40;
+----+
| x |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 10 |
+----+
5 rows in set (0.06 sec)
mysql> INSERT INTO test40 VALUES (10) ON DUPLICATE KEY UPDATE x = x + 10;
Query OK, 2 rows affected (0.07 sec)
mysql> SELECT * FROM test40;
+----+
| x |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 20 |
+----+
5 rows in set (0.00 sec)