태그 보관물: mysql

MySQL Insert 서브 쿼리에서 한개 이상의 리턴인 경우

CREATE TABLE temp (

SELECT id, NAME

FROM USER

)

1. 서브쿼리의 반환 행이 하나인 경우

INSERT INTO temp (id, NAME) SELECT id, NAME FROM user2 WHERE id=‘coozplz’)

2. 서브쿼리의 반환 행이 하나 이상인 경우
INSERT INTO temp (id, NAME) SELECT id, NAME FROM user2

Values 를 제거 한다.

 

 

이 글은 java 카테고리에 분류되었고 태그가 있으며 님에 의해 에 작성되었습니다.

MySQL Compare to Inserting

프로젝트 마다 성격이 다르고 요청하는 부분이 다르지만.. 대부분은 거의 비슷한 요청을 합니다. 이번에 발생한 데이터베이스 입력 부분은 다른 프로젝트에서도 발생될 수 있는 부분이라 정리를 해봤습니다. 현재 프로젝트에서 모든 데이터베이스 입력은 건 by 건으로 동작하고 있습니다.
만약 사용자가 1000명의 데이터 입력을 요청하면..
for(int i=0; i<1000; i++) {
    // 데이터베이스 입력
}
이와 같은 형식으로 하다 보니 데이터베이스에 입력하는데 소요되는 시간이 아주 많이 걸립니다.
그래서 구글링을 해본 결과 MYSQL 데이터베이스에서 입력하는 방법 3가지로 요약됩니다.

  1. INSERT INTO TEST_TABLE VALUES (  , , , ); * 1000
  2. INSERT INTO TEST_TABLE VALUES (,,,),(,,,)… (,,,);
  3. LOAD DATA IN FILE…

빠른 순서대로 하면 3 > 2 > 1 순입니다.

LOAD DATA 파일은 빠르지만 매번 파일을 생성해야 하는 번거러움이 있습니다. 그래서 두번째로 빠른 방법을 선택해서 테스트를 해봤습니다.

INSERT_TEST (
    ID       varchar(20),
   NAME varchar(50)
)

위의 테이블에 각각 2000건씩 입력을 하는 테스트입니다.

Class.forName("com.mysql.jdbc.Driver");

 

Connection conn

    =  DriverManager

        .getConnection(

                "jdbc:mysql://localhost:3306/test?characterEncoding=utf8"

              , "coozplz"

              , "coozplz"

         );

 

Statement stmt1 = conn.createStatement();

long sTime = System.currentTimeMillis();

for(int i=0; i< 2000; i++) {

    String sql = "INSERT INTO insert_test values ('"

                    +i+"', 'test입니다.')";

    stmt1.execute(sql);

}

System.out.println("exec 2000 times "

                +(System.currentTimeMillis() - sTime) / 1000.0);

 

 

Statement stmt2 = conn.createStatement();

long sTime2 = System.currentTimeMillis();

StringBuilder sb = new StringBuilder("INSERT INTO insert_test values ");

for(int i=0; i< 2000; i++) {

    sb.append("('"+i+"','test입니다.')");

    if(i < 2000-1) {

        sb.append(",");

    }

}

stmt2.execute(sb.toString());

System.out.println("exec 1 time "

        +(System.currentTimeMillis() - sTime2) / 1000.0);

최대한 간단하게 만들었습니다.
위에 보면 stmt1.execute(sql) * 2000  아래는 stmt2.execute(sb.tostring()) 이 한번 도는 것을 확인 할 수 있습니다.

해당 결과는

exec 2000 times 64.41
exec 1 time 0.151

2천건에서 간단한 테스트 프로그램으로도 이정도의 차이가 나지만 실제 운영 중인 서버에서는 더 많은 차이가 날 수 있습니다…

이 글은 java 카테고리에 분류되었고 태그가 있으며 님에 의해 에 작성되었습니다.

Mysql Insert 오류

Mysql Insert를  1 행씩 하다 보니 성능이 너무 저질이라… insert into table values (),(),()…()
의 문법을 사용해서 하려고 했는데.. 데이터를 좀 많이 넣으니.. 아래와 같은 오류가 출력되었습니다.


 

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1146237 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2584)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1554)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)

    at com.mysql.jdbc.Connection.execSQL(Connection.java:3170)

    at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1316)

    at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1235)

    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)

    at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:457)

    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:341)

    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:467)

 

한번에 실행할 수 있는 패킷의 양이 정해져 있습니다. 덤으로 콘솔 로그를 무한으로 찍해 놓은 저의 PC도 먹통이 된것 처럼 느려지게 되었습니다. 이클립스 콘솔로그 사이즈를 정해야 겠습니다.

쿼리를 통해 조회해 보니

mysql> show variables like ‘max%’;
+—————————-+———————-+
| Variable_name              | Value                |
+—————————-+———————-+
| max_allowed_packet         | 1048576              |
| max_binlog_cache_size      | 4294963200           |
| max_binlog_size            | 1073741824           |
| max_connect_errors         | 10                   |
| max_connections            | 100                  |
| max_delayed_threads        | 20                   |
| max_error_count            | 64                   |
| max_heap_table_size        | 16777216             |
| max_insert_delayed_threads | 20                   |
| max_join_size              | 18446744073709551615 |
| max_length_for_sort_data   | 1024                 |
| max_prepared_stmt_count    | 16382                |
| max_relay_log_size         | 0                    |
| max_seeks_for_key          | 4294967295           |
| max_sort_length            | 1024                 |
| max_sp_recursion_depth     | 0                    |
| max_tmp_tables             | 32                   |
| max_user_connections       | 0                    |
| max_write_lock_count       | 4294967295           |
+—————————-+———————-+
19 rows in set (0.00 sec)

mysql>

mysql>
mysql>
set global max_allowed_packet= 1024*1024*32;
Query OK, 0 rows affected (0.02 sec)

 

32M 로 설정 완료.. 다시 실행 해봅시다.

이 글은 java 카테고리에 분류되었고 태그가 있으며 님에 의해 에 작성되었습니다.

BATCH 파일을 이용한 MySQL 백업 및 파일 정리

아래와 같은 스크립트를 통해 데이터베이스 백업과 기존 파일 삭제 프로세스를 구동 할 수 있다.
Batch 파일을 생성한 이후에 윈도우 작업 스케줄에 포함 시키면 끝

작업 스케쥴러에 포함하기

제어판 – 관리도구 – 작업 스케쥴러 – 기본 작업 만들기
기본 작업 만들기 마법사를 따라 시간 및 주기를 설정함.
작업은 프로그램 시작으로 설정하고 프로그램 찾아보기를 클릭하여 BAT 파일을 선택합니다.



웹 서비스에 별도의 스레드를 가동하여 처리 하려고 했는데 너무 쉽게 처리를 할 수 있는 방법을 배워버렸습니다… Shell 을 이용하는 방법에 대해 공부를 해야 할 것 같습니다. Shell Programming도 아마 무궁 무진 하지만 간단하게 어떻게 사용하는지 그리고 다른 사람이 프로그래밍한 것을 읽을 수 있게 하면 될 것 같습니다.


@echo off
echo Running dump...
REM 파일명으로 사용하기 위해 시간을 구한다. yyyy-MM-dd_hhmmss
set createTime=%date%_%time:~0,2%%time:~3,2%%time:~6,2%

mysqldump -u[사용자아이디] -p[비밀번호] --databases [DB명] -r [저장경로]\%createTime%.sql

REM sql 파일 용량을 줄이기 위해 압축
7z a %createTime%.zip %createTime%.sql

REM 압축 후 SQL 파일 삭제
del %createTime%.sql

REM 이전 파일들을 삭제하는 프로세스 시작
REM 파일 삭제 기간
set day=-365
echo before %day% day ago files deleting ...

REM TEMP 디렉토리에 있는 1년전 파일들을 삭제한다. 파일들을 삭제한다.
forfiles /p D:\TEMP /m *.sql /d %day% /c &amp;quot;cmd /c del @file&amp;quot;

echo before %day% day ago files deleting finish
echo Done!
이 글은 db 카테고리에 분류되었고 태그가 있으며 님에 의해 에 작성되었습니다.

MySQL str_to_date(string, format)

STR_TO_DATE(str,format) is the inverse of the DATE_FORMAT() function.

STR_TO_DATE() returns a DATETIME value.

The following specifiers may be used in the format string.

The ‘%’ character is required before format specifier characters.

Specifier
Description

%a
Abbreviated weekday name (Sun..Sat)

%b
Abbreviated month name (Jan..Dec)

%c
Month, numeric (0..12)

%D
Day of the month with English suffix (0th, 1st, 2nd, 3rd, ?-)

%d
Day of the month, numeric (00..31)

%e
Day of the month, numeric (0..31)

%f
Microseconds (000000..999999)

%H
Hour (00..23)

%h
Hour (01..12)

%I
Hour (01..12)

%i
Minutes, numeric (00..59)

%j
Day of year (001..366)

%k
Hour (0..23)

%l
Hour (1..12)

%M
Month name (January..December)

%m
Month, numeric (00..12)

%p
AM or PM

%r
Time, 12-hour (hh:mm:ss followed by AM or PM)

%S
Seconds (00..59)

%s
Seconds (00..59)

%T
Time, 24-hour (hh:mm:ss)

%U
Week (00..53), where Sunday is the first day of the week

%u
Week (00..53), where Monday is the first day of the week

%V
Week (01..53), where Sunday is the first day of the week; used with %X

%v
Week (01..53), where Monday is the first day of the week; used with %x

%W
Weekday name (Sunday..Saturday)

%w
Day of the week (0=Sunday..6=Saturday)

%X
Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V

%x
Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

%Y
Year, numeric, four digits

%y
Year, numeric (two digits)

%%
A literal ‘%’ character

%x
x, for any ‘x’ not listed above

이 글은 db 카테고리에 분류되었고 태그가 있으며 님에 의해 에 작성되었습니다.

MySQL 서비스 등록

어제 잘 되던 MySQL 에 접속 할 수 없다는 오류 메시지가 출력 되었습니다.
서비스가 죽어 있나 해서 윈도우 서비스 목록에서 재 시작을 하려 했지만
서비스 목록에서 MySQL 이 없어졌습니다. (급 당황)

혹시나 해서 Console 로 접속해서
C:\>net start MySQL

서비스 이름이 잘못되었습니다.

NET HELPMSG 2185을(를) 입력하면 도움말을 더 볼 수 있습니다.

검색해서 찾아보니 서비스에 등록하는 방법이 있습니다.
C:\Program Files\MySQL\MySQL Server 5.1>mysqld -install
Service successfully installed.

C:\Program Files\MySQL\MySQL Server 5.1>
C:\Program Files\MySQL\MySQL Server 5.1>
C:\Program Files\MySQL\MySQL Server 5.1>
C:\Program Files\MySQL\MySQL Server 5.1>net start MySQL
MySQL 서비스를 시작합니다..
MySQL 서비스가 잘 시작되었습니다.

다시 접속을 시도 하니 잘 됩니다.. ㅎㅎ

이 글은 db 카테고리에 분류되었고 태그가 있으며 님에 의해 에 작성되었습니다.

MySQL 기간 검색 쿼리

-- Date 형 예제
  SELECT 
    userid,
    sender,
    TIME 
  FROM
    cims_received_message 
  WHERE TIME >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) && TIME <= CURDATE() ;
 
-- Datetime 형 예제
SELECT 
  userid,
  sender,
  TIME 
FROM
  cims_received_message 
WHERE TIME >= DATE_SUB(NOW(), INTERVAL 1 MONTH) && TIME <= NOW() ;

Date Type 과 Datetime Type 둘다 상관 없이 됩니다.

이 글은 db 카테고리에 분류되었고 태그가 있으며 님에 의해 에 작성되었습니다.