RPM 설치
--
MySQL은 DBMS를 칭하고
mysql은 명령어를 칭한다.
[root@mysql99 ~]# mkdir /stage
[root@mysql99 ~]# cd /stage/
[root@mysql99 stage]# mkdir mysql
[root@mysql99 stage]#
[root@mysql99 stage]#
[root@mysql99 stage]# cd mysql/
[root@mysql99 mysql]# cp /mnt/hgfs/shared-2/MySQL-* ./
패키지 설치
1. 패스워드 바까라. 뭐 하나 실행하라.
[root@mysql99 mysql]# rpm -Uvh --replacefiles MySQL-server-5.5.44-1.rhel5.i386.rpm
[root@mysql99 mysql]# rpm -Uvh --replacefiles MySQL-server-5.5.44-1.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-server ########################################### [100%]
151228 10:02:14 [Note] /usr/sbin/mysqld (mysqld 5.5.44) starting as process 5793 ...
151228 10:02:14 [Note] /usr/sbin/mysqld (mysqld 5.5.44) starting as process 5799 ...
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h mysql99.ocmkorea.com password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems at http://bugs.mysql.com/
2.
[root@mysql99 mysql]# rpm -Uvh --replacefiles MySQL-client-5.5.44-1.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-client ########################################### [100%]
3.
[root@mysql99 mysql]# rpm -Uvh --replacefiles MySQL-devel-5.5.44-1.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-devel ########################################### [100%]
4.
[root@mysql99 mysql]# rpm -Uvh --replacefiles MySQL-shared-5.5.44-1.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-shared ########################################### [100%]
5.
[root@mysql99 mysql]# rpm -Uvh --replacefiles MySQL-test-5.5.44-1.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-test ########################################### [100%]
그리고
cd /etcinit.d/
ls my*
mysql 생김
./mysql start
데몬 실행
[root@mysql99 mysql]# cd /etc/init.d/
[root@mysql99 init.d]# ls my*
mysql
[root@mysql99 init.d]# ./mysql start
Starting MySQL.. [ OK ]
1. 한거 한다.
[root@mysql99 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
패스워드바꾸기
mysql> set password=password('mysql99');
Query OK, 0 rows affected (0.00 sec)
실행
[root@mysql99 ~]# /usr/bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Enter current password for root (enter for none):
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] n
... skipping.
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | 메타데이터 (딕셔너리정보)
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
데이터가 어디에 저장되어 있는지
mysql> show variables like 'datadir' \G
*************************** 1. row ***************************
Variable_name: datadir
Value: /var/lib/mysql/
1 row in set (0.00 sec)
그냥 만들어 봄
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
데이터를 넣으면
[root@mysql99 ~]# cd /var/lib/mysql/
[root@mysql99 mysql]# ls
ibdata1 mysql99.ocmkorea.com.err RPM_UPGRADE_HISTORY
ib_logfile0 mysql99.ocmkorea.com.pid RPM_UPGRADE_MARKER-LAST
ib_logfile1 mysql.sock test
mysql performance_schema
이곳에 파일이 생긴다
mysql> create dataase world_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 'dataase word_innodb' at line 1
mysql> create database world_innodb;
Query OK, 1 row affected (0.00 sec)
이노디비로 들어가기
[root@mysql99 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use world_innodb
Database changed
그리고나서
source /root/world_innodb.sql
성공 3개 만들어짐
[root@mysql99 mysql]# ls
ibdata1 mysql mysql.sock RPM_UPGRADE_MARKER-LAST
ib_logfile0 mysql99.ocmkorea.com.err performance_schema test
ib_logfile1 mysql99.ocmkorea.com.pid RPM_UPGRADE_HISTORY world_innodb
[root@mysql99 mysql]# cd world_innodb/
[root@mysql99 world_innodb]# ls
City.frm Country.frm CountryLanguage.frm db.opt
show table status;
대소문자 가림(까다로움)
mysql> show create table City;
+------- +----------------------------------------------------------------------------------- ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ -------------------+
| Table | Create Table |
+------- +----------------------------------------------------------------------------------- ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ -------------------+
| City | CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |
+------- +----------------------------------------------------------------------------------- ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ -------------------+
1 row in set (0.00 sec)
mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
껍데기만 복사
mysql> create table CityMyISAM like City;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------------+
| Tables_in_world_innodb |
+------------------------+
| City |
| CityMyISAM |
| Country |
| CountryLanguage |
+------------------------+
4 rows in set (0.00 sec)
데이터는 없다
mysql> select * from CityMyISAM;
Empty set (0.00 sec)
만들수 있는 것인지 확인은 이렇게 한다.
mysql> show engines;
+--------------------+--------- +----------------------------------------------------------------+-------------- +------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+--------- +----------------------------------------------------------------+-------------- +------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| 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 |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+--------- +----------------------------------------------------------------+-------------- +------+------------+
9 rows in set (0.00 sec)
엔진 바꾸기
mysql> alter table CityMyISAM engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
데이터 가져오기
mysql> insert into CityMyISAM select * from City;
Query OK, 4079 rows affected (0.05 sec)
Records: 4079 Duplicates: 0 Warnings: 0
사용가능한 바이너리
mysql> show plugins;
+--------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+--------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+--------------------------+----------+--------------------+---------+---------+
23 rows in set (0.01 sec)
innodb 16k 40억 페이지 64TB 트랜잭션 지원
MyISAM 256TB 트랜잭션 지원 X
----------------------------------------------------------------------------
재배치 작업--------------------------------------------------------재배치 작업
-----------------------------------------------------------------------------
employees 만들기
mysql> create database employees;
Query OK, 1 row affected (0.00 sec)
권한주기
mysql> grant all privileges on employees.*
-> to emp_admin@localhost identified by 'mysql99' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
스크립트 복사
[root@mysql99 ~]# cp /mnt/hgfs/shared-2/employees.sql /root/employees
[root@mysql99 ~]# cp /mnt/hgfs/shared-2/load_* /root/employees
접속해서
mysql -uemp_admin -pmysql99 (employees)
디비로가고
mysql> use employees
스크립트돌리고
mysql> source employees.sql
mysql> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
| titles |
+---------------------+
6 rows in set (0.00 sec)
간단히 조회
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.11 sec)
학사디비 만들기
mysql>
mysql> create database haksadb;
Query OK, 1 row affected (0.01 sec)
mysql> grant all privileges on haksadb.*
-> to haksa_admin@localhost identified by 'mysql99'
-> with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
---------------3개 디비 만들었음 ------------------------------
vi .bash_profile
alias myr='mysql -uroot -pmysql99'
alias mys='mysql -p mysql'
alias myh='mysql -u haksa_admin -p haksadb'
alias myp='mysql -u emp_admin -p employees'
. .bash_profile
공유폴더에
haksa_table or haksa_data
이 두개를 vi 편집기로 복사해서
스크립트를 실행한다.
source haksa_tabel.sql
source haksa_data.sql
mysql> show tables;
+-------------------+
| Tables_in_haksadb |
+-------------------+
| attend |
| circle |
| department |
| fee |
| post |
| professor |
| score |
| student |
| subject |
+-------------------+
9 rows in set (0.01 sec)
select * from student;
+----------+--------------+------------------+-----------+-------+-------+------ +----------------+---------+-----------------------------------+-------------- +---------------+------------+
| stu_no | stu_name | stu_ename | dept_code | grade | class | juya | id_num | post_no | address | tel | phone_no | birth_year |
+----------+--------------+------------------+-----------+-------+-------+------ +----------------+---------+-----------------------------------+-------------- +---------------+------------+
| 20001001 | 김유신 | Kim Yoo-Shin | 40 | 4 | 3 | 야 | 811007-1632013 | 556-820 | 안포리 28번지 | 061)685-7818 | 011- 617-1290 | 1981 |
| 20001015 | 박도준 | Park Do-Jun | 40 | 4 | 1 | 주 | 780116-1580715 | 135-900 | 386번지 | 02)744-6126 | 011- 611-9884 | 1978 |
| 20001021 | 이상길 | Lee Sang-Gil | 40 | 4 | 1 | 주 | 750819-1227014 | 451-800 | 안정리 주공APT 107동 504호 | 031)691-5423 | NULL | 1975 |
| 20041002 | 김유미 | Kim Yoo-Mi | 40 | 3 | 2 | 주 | 830207-2629715 | 545-800 | 덕례리 산 16-1번지 | 061)763-1439 | 010- 617-1290 | 1983 |
| 20041007 | 정인정 | Jeung Yin-Jeung | 40 | 2 | 2 | 주 | 830315-2351225 | 135-794 | 6동 1203호 | 02)723-1078 | 016- 605-7837 | 1983 |
| 20041033 | 연개소문 | Yean Gae-So-Moon | 40 | 3 | 3 | 야 | 810615-1633111 | 550-130 | 280-50번지 | 061)642-9304 | 018- 641-9304 | 1981 |
| 20061011 | 박정인 | Park Jung-In | 40 | 2 | 1 | 주 | 830403-1635213 | 150-051 | 985번지 롯데APT 102-306 | 02)652-2439 | 017- 3142-1294 | 1983 |
| 20061014 | 고혜진 | Ko Hea-Jin | 10 | 2 | 1 | 주 | 870307-2638759 | 500-170 | 삼익APT 101동 102호 | 061)781-5135 | NULL | 1987 |
| 20061048 | 김영호 | Kim Young-Ho | 10 | 4 | 3 | 야 | 860811-1548758 | 506-040 | 라인APT 207동 309호 | 062)678-1010 | 017- 614-7575 | 1986 |
| 20071001 | 장수인 | Jang Soo-In | 40 | 1 | 1 | 주 | 890209-1616822 | 545-080 | 금광APT 108동 1101호 | 061)791-1236 | NULL | 1989 |
| 20071010 | 홍길동 | Hong Gil-Dong | 40 | 1 | 3 | 야 | 880402-1850838 | 550-160 | 해태APT 104동 605호 | 061)642-4034 | 010- 6425-9245 | 1988 |
| 20071022 | 이순신 | Lee Sun-Shin | 10 | 1 | 3 | 야 | 890222-1218818 | 135-794 | 2동 1004호 | 02)745-7667 | 010- 7141-1860 | 1989 |
| 20071300 | 유하나 | Yoo Ha-Na | 50 | 1 | 1 | 주 | 880921-2573717 | 550-260 | 주공APT 204동 512호 | 061)651-5992 | 019- 651-0707 | 1988 |
| 20071307 | 김문영 | Kim Moon-Young | 50 | 1 | 3 | 야 | 880418-2121623 | 135-905 | 31동 102호 | 02)745-5485 | 019- 4624-0460 | 1988 |
| 20071405 | 최차영 | Choi Cha-Young | 50 | 1 | 2 | 주 | 881003-2581516 | 135-786 | 101동 540호 | 02)745-6893 | NULL | 1988 |
+----------+--------------+------------------+-----------+-------+-------+------ +----------------+---------+-----------------------------------+-------------- +---------------+------------+
15 rows in set (0.00 sec)
간단한 실습
등록한 학생중 학생별로 가장 많은 등록금을 납부했을 때 학생의 학번 등록년도 학기 납부 총 액을 출력 하라.
학번 등록년도 학기 오름차순
fee 테이블
mysql> select stu_no, fee_year, fee_term, fee_pay
-> from fee f1
-> where fee_pay=
-> (select max(fee_pay)
-> from fee f2
-> where f1.stu_no = f2.stu_no)
-> order by stu_no, fee_year, fee_term;
+----------+----------+----------+---------+
| stu_no | fee_year | fee_term | fee_pay |
+----------+----------+----------+---------+
| 20001015 | 2000 | 1 | 2000000 |
| 20061011 | 2006 | 1 | 3000000 |
| 20071001 | 2007 | 1 | 3000000 |
| 20071001 | 2007 | 2 | 3000000 |
| 20071010 | 2007 | 1 | 3000000 |
| 20071010 | 2007 | 2 | 3000000 |
| 20071022 | 2007 | 1 | 3000000 |
| 20071022 | 2007 | 2 | 3000000 |
| 20071300 | 2007 | 1 | 3000000 |
| 20071307 | 2007 | 1 | 3000000 |
| 20071307 | 2007 | 2 | 3000000 |
| 20071405 | 2007 | 1 | 3000000 |
+----------+----------+----------+---------+
12 rows in set (0.00 sec)
루트에서 DB 명령어 하기.
[root@mysql99 mysql]# mysqlshow -uroot -pmysql99
+--------------------+
| Databases |
+--------------------+
| information_schema |
| employees |
| haksadb |
| mysql |
| performance_schema |
| test |
| world_innodb |
+--------------------+
3~4가지의 로그 파일 슬로우 로그 파일 즉 쿼리가 느려질 경우 기록되는 파일
mysql> show variables like 'have_%';
+----------------------+----------+
| Variable_name | Value |
+----------------------+----------+
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_partitioning | YES |
| have_profiling | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_symlink | YES |
+----------------------+----------+
14 rows in set (0.00 sec)
파라미터가 있는데 파라미터들의 상태 system v와 status v가 있다
sql mode sql안에서의 환경설정
리눅스에서는 my.cnf에서 모든 환경설정 기록 윈도우는 my.ini
리눅스에서의 .my.cnf이런 파일들은 숨김파일인데 ls -al로 보면 다 보임
/etc/my.cnf는 전역적인
[root@MySQL mysql]# pwd
/usr/share/mysql
[root@MySQL mysql]# ls
my-huge.cnf
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf
현재 mysql 메모리가 소비하는 size를 기준으로 파일을 사용
[root@MySQL mysql]# cp my-large.cnf /etc/my.cnf
[root@MySQL mysql]# cd
[root@MySQL ~]# vi /etc/my.cnf
my sql 3306
ms sql 1433
각기 다른 5개의 파일들은 mysql 디비 운영시 분석이 필요하다
기본값 확인 명령
[root@MySQL mysql]# mysqld --verbose --help
default non default(user의 수정) hidden parameter의 3종류
구종중의 서버의 현재 사용값
mysql> show global variables;
대규모 작업시에는 autocommit를 off하고 작업하는 경우가 많다
mysql> show session variables;
현재 상태값 조회 < 일종의 모니터링 >
mysql> show global status;
현재 모드 확인 결과 현재 설정된 mode는 아직 없음
mysql> show variables like '%mode%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | |
+--------------------------+--------+
5 rows in set (0.00 sec)
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> set @@sql_mode='strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+---------------------+
| @@sql_mode |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)
mysql> set @@sql_mode='strict_trans_tables,traditional';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@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)
mysql> set @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
이런 작업을 my.cnf에 넣을 경우 전역적 작용이 되는 것이고 세션레벨에서만 할 경우 세션레벨에만
영향을 준다
mysql> use world_innodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table t1_null ( c1 int not null, c2 int not null, c3 date not null) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1_null(c2) values(1);
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> select * from t1_null;
+----+----+------------+
| c1 | c2 | c3 |
+----+----+------------+
| 0 | 1 | 0000-00-00 |
+----+----+------------+
1 row in set (0.00 sec)
mysql> insert into t1_null(c2) values(1);
ERROR 1364 (HY000): Field 'c1' doesn't have a default value
mysql> select * from t1_null;
+----+----+------------+
| c1 | c2 | c3 |
+----+----+------------+
| 0 | 1 | 0000-00-00 |
+----+----+------------+
1 row in set (0.00 sec)
1) 잘못된 날짜 형식에 대한 처리
: SQL_MODE가 STRICT_TRANS_TABLES 일때 잘못된 날짜 값을 적재할 경우
날짜가 0이면 0000-00-00으로 적재된다. 날짜가 2015-13-01이면 에러가 발생한다.
mysql> insert into t1_null values(1,2,0);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1_null;
+----+----+------------+
| c1 | c2 | c3 |
+----+----+------------+
| 0 | 1 | 0000-00-00 |
| 1 | 2 | 0000-00-00 |
+----+----+------------+
2 rows in set (0.00 sec)
-- SQL_MODE가 STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE 로 설정하면
날짜가 0으로 입력될때도 에러 발생, 날짜가 2015-13-01로 입력되도 에러가 발생한다.
mysql> set @@sql_mode='strict_trans_tables,no_zero_date,no_zero_in_date';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+--------------------------------------------------+
| @@sql_mode |
+--------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t1_null values(2,3,'2015-13-10');
ERROR 1292 (22007): Incorrect date value: '2015-13-10' for column 'c3' at row 1
mysql> insert into t1_null values(2,3,0);
ERROR 1292 (22007): Incorrect date value: '0' for column 'c3' at row 1
엔진 지정에 대한 처리
-- SQL_MODE가 NO_ENGINE_SUBSTITUTION으로 지정되지 않았을 경우
잘못된 엔진 이름을 기입하여도 에러가 발생하지 않고 디폴트 엔진으로 기입된다.
mysql> set @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> create table t2_engine(id int) engine=woleihsbgjsdf;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show create table t2_engine;
+-----------+-----------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------+
| t2_engine | CREATE TABLE `t2_engine` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 반대로 SQL_MODE가 NO_ENGINE_SUBSTITUTION 설정되어 있다면, 부정확한 엔진 지정 입력 사항에 대해 에러를 발생시킨다.
mysql> create table t3_engine(id int) engine=xxxxxx;
ERROR 1286 (42000): Unknown storage engine 'xxxxxx'
mysql> show create table t3_engine;
ERROR 1146 (42S02): Table 'world_innodb.t3_engine' doesn't exist
'스터디북' 카테고리의 다른 글
| [12/29] 12c RAC New Feature (0) | 2015.12.29 |
|---|---|
| <12/29> 이력서,result cache,12c RAC (0) | 2015.12.29 |
| <12/27> Rac11g ASM (0) | 2015.12.27 |
| <12/26> 성능고도화 Rac11g (0) | 2015.12.26 |
| [12/24] MySQL APM 연동 설치 (0) | 2015.12.26 |
