[MySQL/MariaDB] 귀찮은 쿼리 한번에 생성하기 (SELECT, UPDATE, INSERT, DELETE)

 

[MySQL/MariaDB] 귀찮은 쿼리 한번에 생성하기 (SELECT, UPDATE, INSERT, DELETE)

 

 - 피곤하다 한글자라도 덜 치고 그 시간에 쉬자

 

 

1. 인트로

 - 매번 SELECT 문을 직접 치고

 - 매번 UPDATE 문을 직접 치고

 - 매번 INSERT 문을 직접 치고

 - 매번 DELETE 문을 직접 치는게 피곤하다

 

 

 

2. 쿼리

 - 아래 쿼리 상단의 테이블명만 입력 후 쿼리를 실행하면 된다 (덜 피곤하다)

/*    귀찮은 SELECT, UPDATE, DELETE, INSERT QUERY 한방에 생성하기 (feat. mybatis,ibatis)
     Created by 닢향    
     http://niphyang.tistory.com */



SET @tableName = 'TABLE NAME'; /* 테이블명을 적고 실행하면 끝 */

SELECT 
     'NORMAL' as type,
     concat('insert into ',TABLE_NAME,' (\n',GROUP_CONCAT('',COLUMN_NAME,'\n'),')values(\n',GROUP_CONCAT('',COLUMN_NAME,'\n'),')') as insertQuery,
     concat('update ',TABLE_NAME,' set \n',GROUP_CONCAT(COLUMN_NAME,' = ',COLUMN_NAME,'\n')) as updateQuery,
     concat('select ',GROUP_CONCAT('A.',COLUMN_NAME,'\n'),' from ',TABLE_NAME ,' A') as selectQuery1,
     concat('select ',GROUP_CONCAT('A.',COLUMN_NAME,'\n'),' from ',TABLE_NAME ,' A','\n where ', GROUP_CONCAT('A.', COLUMN_NAME,' = ',COLUMN_NAME,'\n' SEPARATOR ' and ')) as selectQuery2,
     concat('delete from ',TABLE_NAME) as deleteQuery
FROM 
     INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @tableName

union all

SELECT 
     'IBATIS' as type,
     concat('insert into ',TABLE_NAME,' (\n',GROUP_CONCAT('',COLUMN_NAME,'\n'),')values(\n',GROUP_CONCAT('#',COLUMN_NAME,'#\n'),')') as insertQuery,
     concat('update ',TABLE_NAME,' set \n',GROUP_CONCAT(COLUMN_NAME,' = #',COLUMN_NAME,'#\n')) as updateQuery,
     concat('select ',GROUP_CONCAT(COLUMN_NAME,'\n'),' from ',TABLE_NAME) as selectQuery1,
     concat('select ',GROUP_CONCAT('A.',COLUMN_NAME,'\n'),' from ',TABLE_NAME ,' A' ,'\n where ',GROUP_CONCAT('A.', COLUMN_NAME,' = #',COLUMN_NAME,'#\n' SEPARATOR ' and ')) as selectQuery2,
     concat('delete from ',TABLE_NAME) as deleteQuery
FROM 
     INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @tableName

union all

SELECT 
     'MYBATIS' as type,
     concat('insert into ',TABLE_NAME,' (\n',GROUP_CONCAT('',COLUMN_NAME,'\n'),')values(\n',GROUP_CONCAT('#{',COLUMN_NAME,'}\n'),')') as insertQuery,
     concat('update ',TABLE_NAME,' set \n',GROUP_CONCAT(COLUMN_NAME,' = #{',COLUMN_NAME,'}\n')) as updateQuery,
     concat('select ',GROUP_CONCAT('A.',COLUMN_NAME,'\n'),' from ',TABLE_NAME ,' A') as selectQuery1,
     concat('select ',GROUP_CONCAT('A.',COLUMN_NAME,'\n'),' from ',TABLE_NAME ,' A' ,'\n where ',GROUP_CONCAT('A.',COLUMN_NAME,' = #{',COLUMN_NAME,'}\n' SEPARATOR ' and ')) as selectQuery2,
     concat('delete from ',TABLE_NAME) as deleteQuery
FROM 
     INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @tableName ;

 

3. 실행결과

 - mybatis, ibatis에서 바로 사용할 수 있도록 생성된다 

 

 

4. 끝

 - 피곤한 개발자 피로그래머

 - 나도 피곤하고 여러분도 피곤할테니 마음껏 긁으라고 우클릭 방지는 해놓지 않았으나 양심상 출처는 밝혀주자