MySQL 함수 프로시저가 안만들어진다면
MySQL 에서 프로시저나 함수를 만들때 ERROR 1418 에러가 나면서 생성이 안될때에는 아래처럼 하면 된다. SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators'; SET GLOBAL log_bin_trust_function_creators = ON;
글
MySQL Length and Maximum Size by Data Type
TINYINT[n] [UNSIGNED] [ZEROFILL] : -128 ~ 127 (UNSIGNED then 0 ~ 255) SMALLINT[n] [UNSIGNED] [ZEROFILL] : -32768 ~ 32767 (UNSIGNED then 0 ~ 65535) MEDIUMINT[n] [UNSIGNED] [ZEROFILL] : -8388608 ~ 8388607 (UNSIGNED then 0 ~ 16777215) INT[n] [UNSIGNED] [ZEROFILL] : -2147483648 ~ 2147483647 (UNSIGNED then 0 ~ 4294967295) : All operations are done with "signed BIGINT". so, if you are outside the range of "signed BIGINT", such as addition multiplication, the value will be wrong. INTEGER[n] [UNSIGNED] [ZEROFILL] : -2147483648 ~ 2147483647 (UNSIGNED then 0 ~ 4294967295) BIGINT[n] [UNSIGNED] [ZEROFILL] : -9223372036854775808 ~ 9223372036854775807 (UNSIGNED then 0 ~ 18446744073709551615) FLOAT[n,d] [ZEROFILL] : -3.402823466E+38 ~ -1.175494351E-38, 0 and : 1.175494351E-38 ~ 3.402823466E+38 : You can not set unsigned. DOUBLE[n,d] [ZEROFILL] : -1.7976931348623157E+308 ~ -2.2250738585072014E-308, 0 and : 2.2250738585072014E-308 ~ 1.7976931348623157E+308 ...
MySql : packets larger than max_allowed_packet are not allowed
mysql folder > my.ini [mysqld] section max_allowed_packet=4M edit 256M ( 1024 is Maximum Size, 256 Recommend )
MySQL Workbench 에서 DB Connection Time 수정
MySQL Workbench > Edit > Preferences > SQL Editor > MySQL Session > 600 > 86400 (24 hour)
MySQL Workbench Safe Mode Off
MySQL Workbench 에서 수정,삭제가 안될때 Edit > Preferences... SQL Editor > Other > "Safe Updates" UnCheck 쿼리로 하려면 SET SQL_SAFE_UPDATES =0;
MS-SQL : ROW_NUMBER 순번으로 WHERE 찾기
SELECT ranks,field1,field2 FROM( SELECT ROW_NUMBER() OVER (ORDER BY field1,field2 ASC) AS ranks ,field1 ,field2 FROM TABLE_NAME ) A WHERE ranks = 18 ORDER BY field1,field2 ASC
MS-SQL : 다른 DB 에서 SELECT 한걸 INSERT
insert into COPY_TABLE_NAME select FIELD1,FIELD2,FIELD3,FIELD4,FIELD5 from [127.0.0.1].[DBNAME].[dbo].[ORIGINAL_TABLE_NAME]
MS-SQL : TABLE의 컬럼 정보 가져오기
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '테이블이름' ORDER BY ordinal_position
MS-SQL : 프로시저 검색하기
SELECT * FROM sysobjects WHERE id IN ( SELECT id FROM syscomments WHERE text LIKE '%프로시저이름1%' )
MS-SQL : 함수의 수정된 날짜 확인하기
SELECT name ,type_desc ,create_date ,modify_date FROM sys.sql_modules sm inner join sys.all_objects ao on (sm.object_id = ao.object_id) where name in ( '함수이름1', '함수이름2', '함수이름3' ) * Function 의 생성,수정 일시를 확인할 수 있다.
MS-SQL : SELECT 한것을 INSERT 하기
INSERT INTO A_TABLE SELECT ROW_NUMBER() OVER(ORDER BY NUM ASC) As ROW_NUM ,b.ITEMNAME ,'20140624' ,'001' ,NULL ,'c.YYYYMMDD' FROM B_TABLE b LEFT JOIN C_TABLE c ON (b.ITEMNAME = c.ITEMNAME) WHERE 1=1 * 필드는 A_TABLE 에 맞춰서 '' 빈값이나 B_TABLE 에 있는 값, 또는 B_TABLE 과 JOIN 한 다른 TABLE의 값을 가져와 넣는다. WHERE 조건을 맞춰주면 좋다.
MS-SQL : 뷰에 인덱스 걸기
ALTER VIEW [dbo].[뷰테이블명] -------------- WITH SCHEMABINDING -------------- AS * text 같은 필드가 있으면 걸 수 없다.