MySQL 관련 TIP 정리

################################################################################ # phpmyadmin에서 고정으로 패스워드 등록해 놓는 방법 ################################################################################ // config.inc.php 파일에 다음과 같이 설정해 놓는다. /* Authentication type */ $cfg[‘Servers’][$i][‘auth_type’] = ‘config’; /* Server parameters */ $cfg[‘Servers’][$i][‘host’] = ‘localhost’; $cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’; $cfg[‘Servers’][$i][‘compress’] = false; /* Select mysql if your server does not have mysqli */ $cfg[‘Servers’][$i][‘extension’] = ‘mysqli’; $cfg[‘Servers’][$i][‘AllowNoPassword’] = false; $cfg[‘Servers’][$i][‘user’] = […]

MySQL Tips

MySQL 무중단 Dump 뜨기, InnoDB이고 트랜잭션을 사용중일 때 –skip-add-locks –single-transaction –skip-lock-tables 테이블별 사이즈 보기 SELECT table_name, table_rows, round(data_length/(1024*1024),2) as ‘DATA_SIZE(MB)’, round(index_length/(1024*1024),2) as ‘INDEX_SIZE(MB)’ FROM information_schema.TABLES where table_schema = ‘rewardad’ GROUP BY table_name ORDER BY data_length DESC; 셀렉트로 Dump 뜨기 mysql -urad -p@1emdrewarddb -e”select * from account” rewardad > result.sql MySQL Timezone 설정하기 참고 : http://dev.mysql.com/doc/refman/5.5/en//time-zone-support.html […]

MySQL Federated 엔진 테이블 만들기

CREATE TABLE IF NOT EXISTS `game_session_2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `app_id` varchar(36) NOT NULL, `app_version` varchar(5) NOT NULL, `mnc` varchar(10) NOT NULL, `os` int(1) NOT NULL, `os_version` varchar(10) NOT NULL, `device_model` varchar(100) NOT NULL, `s_time` datetime NOT NULL, `e_time` datetime NOT NULL, `session_length` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=FEDERATED […]

MySQL Function 사용법 및 Tips

Function 사용 예제 DROP FUNCTION IF EXISTS getTotals # CREATE FUNCTION getTotals(d_id VARCHAR(50), start_date VARCHAR(30), end_date VARCHAR(30)) RETURNS INT(10) BEGIN DECLARE totals INT(10); SET totals = ( SELECT COUNT(*) AS total FROM device_info WHERE DATE_FORMAT(CONVERT_TZ(device_info.create_time, "UTC", "Asia/Seoul"), "%Y-%m-%d %H:%i:%S")>=start_date AND DATE_FORMAT(CONVERT_TZ(device_info.create_time, "UTC", "Asia/Seoul"), "%Y-%m-%d %H:%i:%S")<=end_date AND device_info.device_id = d_id ); RETURN totals; END # SELECT […]

MySQL Procedure Function 예제

mysql> mysql> CREATE TABLE Employee( -> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> ); Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) -> values (1,'Jason', 'Martin', '19960725', […]