기초공부 - (1) sql_mode
2024. 1. 23. 21:43ㆍMySQL/Class
반응형
# sql_mode는 우리가 쿼리를 날렸을 때 문법과 유효성 검사의 기준을 바꿔주는 시스템 변수입니다. 이것은 global 및 session에서 설정을 각각 할 수 있어서 사용자에 따라서 문법과 유효성 검사가 달라질 수있습니다.
1. 설정 방법
(1) my.cnf 수정
$ vi /etc/my.cnf
sql_mode=’STRICT_TRANS_TABLES’
(2) mysql.server 수정
$ vi /usr/bin/mysql.server
(3) set
mysql> set sql_mode=‘STRICT_TRANS_TABLES’;
or
mysql> set globl_mode=‘STRICT_TRANS_TABLES’;
# default
sql_mode=‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’
2. 설정값
# 세션모드로 설정하여 sql_mode 포함되어 있는 값들에 대해서 테스트를 진행해 보겠습니다.
(1) ONLY_FULL_GROUP_BY
# ONLY_FULL_GROUP_BY 사용할 경우
# sql_mode 확인
mysql> show variables like 'sql_mode';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| sql_mode | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.00 sec)
mysql> select name,count(*) from dept group by name;
+------------+----------+
| name | count(*) |
+------------+----------+
| golf | 1 |
| basketball | 2 |
| baseball | 1 |
| football | 2 |
+------------+----------+
4 rows in set (0.00 sec)
# 쿼리문에 group by 를 제외시키고 실행 시켰습니다.
mysql> select name,count(*) from dept;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.dept.name'; this is incompatible with sql_mode=only_full_group_by- 위 예제를 보면 당연히 문법에 문제가 있기 때문에 에러가 출력되는 것은 당연합니다.
- 그러나 ONLY_FULL_GROUP_BY 를 제외시켰을 경우는 어떻게 될지 볼까요?
# ONLY_FULL_GROUP_BY 사용하지 않을 경우
# sql_mode 확인
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
# 쿼리문에 group by 를 제외시키고 실행 시켰습니다.
# 쿼리가 에러없이 실행이 되었습니다.
mysql> select name,count(*) from dept;
+------+----------+
| name | count(*) |
+------+----------+
| golf | 6 |
+------+----------+
1 row in set (0.00 sec)- 에러는 발생되지 않고 뭔가 다른 결과물이 나왔습니다.
- 잘못된 쿼리임에도 불구하고 에러를 발생되지 않는다는 것은 개인적으로는 불필요한 기능이 아닐까 라는 생각이듭니다.
(2) STRICT_TRANS_TABLES
# 설정된 컬럼의 길이보다 큰 값이 들어올 경우 에러를 출력합니다.
# 컬럼 길이 확인
mysql> show create table dept;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dept | CREATE TABLE `dept` (
`no` int DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`reg_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# name 컬럼값 10보다 큰 값을 넣어보겠습니다.
# STRICT_TRANS_TABLES 사용할 경우
# sql_mode 확인
mysql> set sql_mode ='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| sql_mode | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)
# 10자 이상되는 값을 입력하여 에러 발생
mysql> insert into dept values(70,'basketballbasketball',now());
ERROR 1406 (22001): Data too long for column 'name' at row 1
# STRICT_TRANS_TABLES 사용하지 않을 경우
# sql_mode 확인
mysql> set sql_mode ='';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.01 sec)
# 10자 이상되는 문자를 입력, 에러 발생되지 않음.
mysql> insert into dept values(70,'basketballbasketball',now());
Query OK, 1 row affected, 2 warnings (0.00 sec)
# 최대값10자까지만 값이 들어가고 이후 문자를 잘리는 것을 볼 수 있습니다.
mysql> select * from dept;
+------+------------+------------+
| no | name | reg_date |
+------+------------+------------+
| 10 | golf | 2024-01-23 |
| 20 | basketball | 2024-01-23 |
| 20 | baseball | 2024-01-23 |
| 40 | football | 2024-01-23 |
| 50 | football | 2024-01-23 |
| 60 | basketball | 2024-01-23 |
| 70 | basketball | 2024-01-23 |
+------+------------+------------+
7 rows in set (0.00 sec)
- 비록 데이터는 잘려서 들어가지만, 데이터 수집을 한다면 유실이 발생되지 않아 유용한 기능이 아닐까라는 생각이 듭니다.
(3) NO_ZERO_IN_DATE
# 월 또는 일 날짜에 00일을 허용할 것인지를 판단합니다.
# NO_ZERO_IN_DATE 사용할 경우
# sql_mode 확인
mysql> set sql_mode = 'NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| sql_mode | NO_ZERO_IN_DATE |
+---------------+-----------------+
1 row in set (0.00 sec)
# 00월 또는 00일 입력
# warning을 보여주고 입력한 값은 무시되고, 0000-00-00 으로 입력됩니다.
mysql> insert into dept values(80,'soccer','2023-00-11');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into dept values(80,'soccer','2023-01-00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from dept;
+------+------------+------------+
| no | name | reg_date |
+------+------------+------------+
| 10 | golf | 2024-01-23 |
| 20 | basketball | 2024-01-23 |
| 20 | baseball | 2024-01-23 |
| 40 | football | 2024-01-23 |
| 50 | football | 2024-01-23 |
| 60 | basketball | 2024-01-23 |
| 70 | basketball | 2024-01-23 |
| 80 | soccer | 0000-00-00 |
| 80 | soccer | 0000-00-00 |
+------+------------+------------+
9 rows in set (0.00 sec)
# NO_ZERO_IN_DATE 사용하지 않을 경우
# sql_mode 확인
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
# 00월, 00일 을 입력하였을 경우, 입력한 그대로 값이 들어갑니다.
mysql> insert into dept values(90,'tennis','2023-00-11');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept values(90,'tennis','2023-01-00');
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept;
+------+------------+------------+
| no | name | reg_date |
+------+------------+------------+
| 10 | golf | 2024-01-23 |
| 20 | basketball | 2024-01-23 |
| 20 | baseball | 2024-01-23 |
| 40 | football | 2024-01-23 |
| 50 | football | 2024-01-23 |
| 60 | basketball | 2024-01-23 |
| 70 | basketball | 2024-01-23 |
| 80 | soccer | 0000-00-00 |
| 80 | soccer | 0000-00-00 |
| 90 | tennis | 2023-00-11 |
| 90 | tennis | 2023-01-00 |
+------+------------+------------+
11 rows in set (0.00 sec)
# 00월 또는 00일로 입력하였을 경우 해당 월 또는 일에 대해서만 00을 허용해주고 있습니다.
(4) NO_ZERO_DATE
# NO_ZERO_DATE 를 사용 유무와 상관없이 데이터는 문제 없이 동일한 값으로 insert 가 됩니다.
# 다만 '0000-00-00' 값에 대해 warning을 보여주느냐 안보여주느냐에 차이입니다.
# NO_ZERO_DATE 사용할 경우 : 0000-00-00 값일 경우 warning을 보여주고 있습니다.
mysql> set sql_mode='NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into dept values(80,'tennis','2023-01-00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept values(80,'tennis','0000-01-00');
Query OK, 1 row affected (0.00 sec)
# warning이 보여지고 있습니다.
mysql> insert into dept values(80,'tennis','0000-00-00');
Query OK, 1 row affected, 1 warning (0.00 sec)
# NO_ZERO_DATE 사용하지 않을 경우
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept values(80,'tennis','2023-01-00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept values(80,'tennis','0000-01-00');
Query OK, 1 row affected (0.01 sec)
mysql> insert into dept values(80,'tennis','0000-00-00');
Query OK, 1 row affected (0.00 sec)
(5) ERROR_FOR_DIVISION_BY_ZERO
# 0 으로 나눴을 때 warning을 보여주느냐 안보여주느냐의 차이입니다.
# warning만 보여질뿐 다른 어떤 영향에도 미치지 않습니다.
# ERROR_FOR_DIVISION_BY_ZERO 사용하였을 경우 : warning을 보여주고 있습니다.
mysql> set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)
mysql> select 10/0;
+------+
| 10/0 |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)
# ERROR_FOR_DIVISION_BY_ZERO 사용하지 않을 경우
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select 10/0;
+------+
| 10/0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
(6) NO_ENGINE_SUBSTITUTION
# 설지되지 않은 storage engine 지정할 시 기본 warning을 보여주는지 판단합니다.
# 설치되지 않은 엔진 확인
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ndbinfo | NO | MySQL Cluster system information storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)
- 설치되지 않은 federated 엔진을 사용해보겠습니다.
# NO_ENGINE_SUBSTITUTION 사용할 경우
# sql_mode 확인
mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.01 sec)
# 테이블 생성 : 설치되지 않은 engine 선택
mysql> create table temp_engine (a int) engine=federated;
ERROR 1286 (42000): Unknown storage engine 'federated'
# 설치되지 않은 engine 변경
mysql> alter table temp_dept engine=federated;
ERROR 1286 (42000): Unknown storage engine 'federated'
# NO_ENGINE_SUBSTITUITION 사용하지 않을 때
# sql_mode 확인
mysql> set sql_mode = '';
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
# federated 엔진 사용 : warning 을 보여주며 테이블이 생성되었습니다.
mysql> create table temp_engine (a int) engine=federated;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
# federated 엔진은 사용되지 않고 기본값인 innodb를 사용하고 있습니다.
mysql> show create table temp_engine;
+-------------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------+
| temp_engine | CREATE TABLE `temp_engine` (
`a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# federated 엔진으로 수정을 해보았습니다. 마찬가지로 warning을 보여주며 변경이 되었습니다.
mysql> alter table temp_engine engine=federated;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1
# 생성했을 때와 마찬가지로 federated 엔진은 사용되지 않고 기본값인 innodb를 사용하고 있습니다.
mysql> show create table temp_engine;
+-------------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------+
| temp_engine | CREATE TABLE `temp_engine` (
`a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# myisam을 수정했을 때는 설치가 되어있기 때문에 변경이 가능한 것을 볼 수 있습니다.
mysql> alter table temp_engine engine=myisam;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table temp_engine;
+-------------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------+
| temp_engine | CREATE TABLE `temp_engine` (
`a` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
디비의 특성상 설정을 다르게 줘서 유용하게 사용할 수도 있을 것 같습니다.
그러나 개인적인 생각으로는 그냥 default값을 쓰는게 어떤가 싶습니다.^^
반응형
'MySQL > Class' 카테고리의 다른 글
| 기초공부 - (3) sleep 세션 정리 하기(interactive_timeout, wait_timeout) (0) | 2024.01.30 |
|---|---|
| 기초공부 - (2) process & thread (0) | 2024.01.26 |
| HA 구성 - (5) MHA : ERROR편 (0) | 2024.01.19 |
| HA 구성 - (4) MHA (0) | 2024.01.18 |
| HA 구성 - (3) replication의 동작원리 (0) | 2024.01.08 |