riberio

-- MySql : 5.7 이상, MariaDB : 10 이상 SELECT VERSION(); -- 컬럼 추가 ALTER TABLE sbtest1 ADD COLUMN reverse_pad CHAR(60) GENERATED ALWAYS AS (REVERSE(pad)) VIRTUAL;

CREATE TABLE `t_holiday` ( `HOLIDAY` DATE NOT NULL, `TITLE` VARCHAR(20), PRIMARY KEY (`HOLIDAY`) ); CREATE TABLE t(n INT); INSERT INTO t VALUES(1); -- 생성할 날 만큼 임시 데이터 생성 INSERT INTO t SELECT * FROM t; -- INSERT INTO t_holiday SELECT d -- , weekday(d) wd , CASE WHEN WEEKDAY(d) = 5 THEN '토요일' WHEN WEEKDAY(d) = 6 THEN '일요일' END FROM ( SELECT @rnum:=@rnum+1 AS rownum, DATE(ADDDATE('2020-01-01', INTE..

ALTER TABLE '테이블명' ADD COLUMN `컬럼명` VARCHAR(1) NULL AFTER `순서컬럼`, ALGORITHM=INPLACE, LOCK=NONE; myinfrabox.tistory.com/61 jsonobject.tistory.com/515

SELECT *, (6371*acos(cos(radians(37.5666805))*cos(radians(P_LAT))*cos(radians(P_LON) -radians(126.9784147))+sin(radians(37.5666805))*sin(radians(P_LAT)))) AS distance FROM TABLE_NAME HAVING distance

/usr/local/mysql/var/error.log 확인 후 2019-07-19 17:06:48 0 [Note] Recovering after a crash using tc.log 2019-07-19 17:06:48 0 [ERROR] Can't init tc log 2019-07-19 17:06:48 0 [ERROR] Aborting 위와 같이 tc.log 초기화 문제로 중지 될 경우 tc.log 삭제 후 서버 시작 할 것

SELECT ordergoodsidx,GROUP_CONCAT( optionitemno SEPARATOR ',')FROM t_order_optionWHERE ordergoodsidx IN (473139)GROUP BY ordergoodsidx

테스트 쿼리 SELECT * FROM ( SELECT VM.*FROM V_MEMBER VMORDER BY VM.NO DESC ) A-- LIMIT 0, 10 ; 위 서브쿼리의 정렬이 정상적으로 되지 않을 경우 시스템 변수를 사용하면 적용이 되더라 ;; SELECT * FROM ( SELECT VM.*FROM V_MEMBER VM , (SELECT @ROWNUM := 0) TMPORDER BY VM.NO DESC ) A-- LIMIT 0, 10 ; 실행 계획을 보면 idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARYALL222DERIVEDsystem1 Using temporary; Using filesort2DERIVEDmALLPRIMARY..