"); $db = mysql_select_db($db_name, $connect); if($db == 0) { die("데이터베이스를 찾을 수 없거나 접근 권한이 없습니다."); } if($connect) { echo "DB연결"; } else { echo "db연결 실패"; } mysql_query("SET NAMES 'euc-kr'"); //--------------------------------------------------------------------- //쿼리 질의 테스트 //--------------------------------------------------------------------- //http://blog.naver.com/digimon1740?Redirect=Log&logNo=110171654155 $query = "SELECT version()"; $result = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); $row=mysql_fetch_row($result); echo "
"; echo $row[0]; /* while ($row = mysql_fetch_assoc($result)) { echo $row['firstname']; echo $row['lastname']; echo $row['address']; echo $row['age']; echo $row[0]; } while ($row=mysql_fetch_row($result)) { echo $row[0]; foreach(explode("','",substr($row[1],6,-2)) as $v) { print("$v"); } } */ //모두 삭제 $query = "delete from `test`.`test` where (test >= '0')"; $res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); //22만 입력 $start_time = microtime(true); $max_count = 5000; for($i=0; $i<$max_count; $i++) { $query = "insert into `test`.`test` (`test`) values ('22')"; $res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); } $diff_time = microtime(true) - $start_time; echo "
"; echo "진행된 시간 : ". $diff_time. "초"; echo "
"; echo "입력된 갯수 : ". $max_count. "개"; /* insert into test (aaaa,bbbb) values(11, 22); insert into test values(22222, 22); delete from test where aaaa = 11; delete from test where aaaa >= 11; update test set aaaa = 10 where aaaa = 3333; select p.aaaa, p.bbbb from test p where p.aaaa >= 10; */ //--------------------------------------------------------------------- //mysql procedure에서 동적으로 테이블명 설정하기.|작성자 완전컴맹 //http://blog.naver.com/beabeak?Redirect=Log&logNo=50176668120 //http://blog.naver.com/chunsj78?Redirect=Log&logNo=50157083671 //http://blog.naver.com/javalove93?Redirect=Log&logNo=130166526849 //http://cafe.naver.com/q69/119958?social=1 //http://cafe.naver.com/pentaeduclub/14840 //http://cafe.naver.com/q69/119962 //http://cafe.naver.com/inline8949ok/160 //http://cafe.naver.com/masterexcel/511 //http://blog.naver.com/javalove93?Redirect=Log&logNo=130166526849 //http://blog.naver.com/chunsj78?Redirect=Log&logNo=50157083671 //http://firstboos.tistory.com/234 //http://www.jhpark.or.kr/320 //http://blog.naver.com/beabeak?Redirect=Log&logNo=50176668120 //--------------------------------------------------------------------- /* # procedure 생성 스크립트 drop procedure sp_InsertTest; DELIMITER // CREATE PROCEDURE sp_InsertTest (IN TableName varchar(20), IN NAME varchar(20), IN phone VARCHAR(20)) BEGIN SET @sql = CONCAT('insert into ', TableName ,' (tname, tphone) values (?,?)'); PREPARE s1 from @sql; SET @name = NAME; SET @phone = phone; EXECUTE s1 USING @name, @phone; END // DELIMITER ; # 실행 하기. call sp_insertTest ('testTb', 'testName', '01022220000'); ------------ //http://kr1.php.net/manual/en/function.print.php $PROC_QUERY = <<< QUERY_CODE DELIMITER; DROP PROCEDURE IF EXISTS P_InsertTest; CREATE PROCEDURE P_InsertTest (IN vaTableName VARCHAR(20), IN nMax INT, IN nVal INT) BEGIN DECLARE val INT DEFAULT 0; DECLARE cnt INT DEFAULT 0; DECLARE err INT DEFAULT '0'; DECLARE str VARCHAR(20); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = -1; START TRANSACTION; //SET @sql = CONCAT('insert into ', vaTableName ,' ('tname') values (?)'); //PREPARE s1 from @sql; //SET @name = NAME; //SET @phone = phone; // WHILE cnt <= nMax DO // EXECUTE s1 USING @name; // END WHILE; WHILE cnt <= nMax DO INSERT INTO vaTableName VALUES (nVal); // 성공 // SET val = val + 1; // SELECT column1 INTO result FROM table1 WHERE ...; // SELECT reslut; END WHILE; IF err < 0 THEN ROLLBACK; ELSE COMMIT; END IF; COMMIT; END // DELIMITER ; QUERY_CODE; */ echo "
"; $query = "select count(*) from test where test >= 0"; $result = mysql_query($query); $rs = mysql_fetch_row($result); $count = $rs[0]; echo "총 갯수 : ". $count. "
"; //모두 삭제 $query = "delete from `test`.`test` where (test >= 0)"; $res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); //22만 입력 $start_time = microtime(true); //프로시저와 트랜잭션을 사용하게 되면. 약간의 성능 향상은 가져올 수 있지만. //- 처리의 난해함 //- 오작동으로 인한 과부하와 멈춤 등에 문제가 있을지 모른다. //http://kr1.php.net/manual/en/function.print.php //http://cafe.naver.com/supedragon/11 //CREATE DEFINER=`root`@`localhost` PROCEDURE `P_InsertTest` (IN _vaTableName VARCHAR(20), IN _nMax INT, IN _nVal INT) //CREATE PROCEDURE P_InsertTest (IN _vaTableName VARCHAR(20), IN _nMax INT, IN _nVal INT) $TABLE = 'test'; $PROC_QUERY = <<< QUERY_CODE CREATE DEFINER=`root`@`localhost` PROCEDURE `P_InsertTest` ( _vaTableName VARCHAR(20), _nMax INT, _nVal INT) BEGIN DECLARE val INT DEFAULT 0; DECLARE cnt INT DEFAULT 0; DECLARE err INT DEFAULT '0'; DECLARE str VARCHAR(20); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = -1; SET str = _vaTableName; WHILE cnt <= _nMax DO INSERT INTO $TABLE VALUES (cnt); flush privileges; SET cnt = cnt + 1; END WHILE; COMMIT; END QUERY_CODE; echo "
"; echo $PROC_QUERY; //'print $text;' Will output the string. Very handy for storing HTML. // $query = "DROP PROCEDURE IF EXISTS P_InsertTest"; $res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); //INSERT 여러번 하기 $query = $PROC_QUERY; $res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); echo "
"; echo "프로시져 생성함"; $query = "call P_InsertTest( 'test', $max_count, 22 )"; $res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); echo "
"; echo "프로시져 실행함"; $diff_time = microtime(true) - $start_time; echo "
"; echo "진행된 시간 : ". $diff_time. "초"; echo "
"; echo "입력된 갯수 : ". $max_count. "개"; $query = "select count(*) from test where test >= 0"; $result = mysql_query($query); $rs = mysql_fetch_row($result); $count = $rs[0]; echo "총 갯수 : ". $count. "
"; //------------------------------------------------------------------------------------------------------------ //http://dev.mysql.com/doc/refman/5.1/en/call.html //http://dev.mysql.com/doc/refman/5.1/en/insert.html //http://dev.mysql.com/doc/refman/5.1/en/insert-select.html //http://dev.mysql.com/doc/refman/5.1/en/insert-select.html //http://dev.mysql.com/doc/refman/5.1/en/replication-features-limit.html //http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-locking.html //http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html //http://search.naver.com/search.naver?ie=UTF-8&query=UNLOCK+TABLES //http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-transactions.html //http://dev.mysql.com/doc/refman/5.1/en/call.html //http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-locking.html //http://blog.naver.com/hostinglove?Redirect=Log&logNo=20094477915 //http://jptrans.naver.net/j2k_frame.php/korean/dev.mysql.com/doc/refman/4.1/ja/lock-tables.html //http://jptrans.naver.net/j2k_frame.php/korean/dev.mysql.com/doc/refman/4.1/ja/flush.html //http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-transactions.html //http://dev.mysql.com/doc/refman/5.1/en/call.html //http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-locking.html //http://cafe.naver.com/friendvirus/31 //http://cafe.naver.com/saneducationcenter/1628 가입 필요 //http://cafe.naver.com/gostorm/3253 가입 필요 //http://cafe.naver.com/realmysql/38 가입 필요 //http://cafe.naver.com/masterexcel/1159 가입 필요 //http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_function&wr_id=249609 //http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_function&wr_id=249654 //https://discussions.apple.com/thread/3838525 //http://www.rootman.co.kr/board/bbs/board.php?bo_table=linux&wr_id=437 //http://blog.naver.com/jmhan92?Redirect=Log&logNo=140017640988 //http://juns0201.tistory.com/54 //http://blog.naver.com/hostinglove?Redirect=Log&logNo=20094477915 //http://search.naver.com/search.naver?ie=UTF-8&query=UNLOCK+TABLES //http://msdn.microsoft.com/en-us/data/aa937712.aspx //.NET Framework Downloads //MDAC Downloads //Jet Downloads //Other Data Access Downloads //Top Downloads //Microsoft Data Access Components (MDAC) 2.8 SP1 //http://www.microsoft.com/ko-kr/download/details.aspx?id=5793 //Microsoft Data Access Components (MDAC) 2.8 Software Development Kit //http://www.microsoft.com/en-us/download/details.aspx?id=21995 //제품 기준 설치 된 ODBC 드라이버 목록 //http://support.microsoft.com/kb/140548/ko //http://msdn.microsoft.com/en-us/data/aa937730.aspx //http://windows.microsoft.com/ko-kr/windows-vista/using-the-odbc-data-source-administrator //http://cafe.naver.com/webagencyalone/179 //http://kr1.php.net/mysql_connect //http://kr1.php.net/manual/en/ini.core.php#ini.sql.safe-mode //http://kr1.php.net/manual/en/mysql.constants.php#mysql.client-flags //http://superman4u.blog.me/40155150998 //http://blog.outsider.ne.kr/340 //http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01_1&m_no=23328&cat1=1399&cat2=1401&cat3=1426&lang=k //http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=user&lang=k&ver_name=USER //http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=user&m_no=23097&cat1=1110&cat2=0&cat3=0&lang=k //http://dev.mysql.com/doc/ //http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=user&m_no=22993&cat1=1109&cat2=0&cat3=0&lang=k //http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=user&m_no=23106&cat1=1111&cat2=0&cat3=0&lang=k //http://dev.mysql.com/downloads/connector/odbc/5.1.html //http://dev.mysql.com/downloads/mirror.php?id=411741 //http://dev.mysql.com/doc/refman/5.6/en/connector-odbc-installation-binary-windows.html //http://support.microsoft.com/kb/110093 //http://www.microsoft.com/en-us/download/details.aspx?id=20065 //http://www.microsoft.com/ko-kr/download/details.aspx?id=20065 //http://mysqldb.tistory.com/16 //http://mysqldb.tistory.com/3 //Variable or condition declaration after cursor or handler declaration //https://www.google.co.kr/?gws_rd=cr&ei=Xl8jUoCHJoaNkwX_k4HQBA#newwindow=1&psj=1&q=variable+or+condition+declaration+after+cursor+or+handler+declaration+mysql //http://benant.wordpress.com/2011/04/09/mysql-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80%EC%A0%80-%EC%A0%95%EC%9D%98-%EC%98%A4%EB%A5%98-error-code-1337-variable-or-condition-declaration-after-cursor-or-handler-declaration/ //http://www.databasejournal.com/features/mysql/perform-row-operations-using-mysql-cursors.html //http://www.databasejournal.com/features/mysql/mysql-cursors-and-loops.html //http://falseisnotnull.wordpress.com/2013/08/01/mariadbmysql-error-handlers-advanced-uses/ //http://www.w3resource.com/mysql/mysql-procedure.php //http://www.w3resource.com/mysql/mysql-procedure.php#MLS //http://mysql-1337.db.quanti.cz/ //http://www.mysqltutorial.org/mysql-cursor/ //http://hideto.iteye.com/blog/194416 //http://www.devshed.com/c/a/MySQL/Error-Handling/3/ //http://librairie.immateriel.fr/fr/read_book/9780596100896/ch05s03 //http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html //http://stackoverflow.com/questions/5097596/having-trouble-doing-multiple-cursors-in-a-mysql-stored-procedure //http://stackoverflow.com/questions/15834433/mysql-cursor-error-1193-unknown-system-variable //http://stackoverflow.com/questions/8192091/syntax-error-for-using-cursor-in-a-stored-procedures //http://stackoverflow.com/questions/14883891/handling-multiple-nested-cursors-in-stored-procedure //http://stackoverflow.com/questions/16976852/is-it-possible-to-declare-cursor-within-if-condition-in-mysql-udf //http://stackoverflow.com/questions/11338006/how-to-use-many-cursors-in-a-single-store-procedure-in-mysql /* DECLARE str VARCHAR(20); SET str = _vaTableName; WHILE _nVal <= _nMax DO INSERT INTO _vaTableName VALUES (_nVal); SET _nVal = _nVal + 1; END WHILE; */ // SET str = _vaTableName; //Table 'test.str' doesn't exist //모두 삭제 $query = "delete from `test`.`test` where (test >= 0)"; $res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); // DECLARE sql VARCHAR(20); // DECLARE name VARCHAR(20); //LOCK is not allowed in stored procedures //LOCK TABLES test WRITE; //lock tables test write; //UNLOCK TABLES; // SET @sql = CONCAT('insert into ', @tablename ,' (', @tablename ,') values (?)'); // PREPARE s1 from @sql; // SET @name = _nVal; // WHILE cnt <= max DO // EXECUTE s1 USING @name; // flush privileges; // SET cnt = cnt + 1; // END WHILE; // SET @sql = CONCAT('insert into ', TableName ,' (tname, tphone) values (?,?)'); // PREPARE s1 from @sql; // SET @name = NAME; // SET @phone = phone; // EXECUTE s1 USING @name, @phone; // SET @sql = CONCAT('insert into ', @tablename ,' (', @tablename ,') values (?)'); // PREPARE s1 from @sql; // SET @val = _nVal; // WHILE cnt <= max DO // EXECUTE s1 USING @val; // flush privileges; // DEALLOCATE PREPARE s1; // SET cnt = cnt + 1; // END WHILE; // WHILE _nVal <= _nMax DO // INSERT INTO @str VALUES (_nVal); // flush privileges; // DEALLOCATE PREPARE s1; // DROP PREPARE s1; // SET _nVal = _nVal + 1; // END WHILE; //인자값을 LOOP에 넣게 되면. +된 결과값이 엉망이 된다. //별도의 cnt 변수를 지정해줘야 한다. //IN_LOOP: LOOP // IF _nVal >= _nMax THEN // LEAVE IN_LOOP; // END IF; // INSERT INTO $TABLE VALUES (_nVal); // flush privileges; // DEALLOCATE PREPARE s1; // DROP PREPARE s1; // SET _nVal = _nVal + 1; //END LOOP IN_LOOP; /* DECLARE max INT DEFAULT 0; DECLARE cnt INT DEFAULT 0; DECLARE err INT DEFAULT '0'; DECLARE str VARCHAR(20); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = -1; SET max = _nMax; SET cnt = 0; SET @val = _nVal; SET @str = _vaTableName; SET @tablename = _vaTableName; SET @sql = CONCAT('insert into ', $TABLE ,' (', $TABLE ,') values (?)'); PREPARE s1 from @sql; SET @val = _nVal; WHILE cnt <= max DO <--- 이거는 하나가 더 +1 된다. INSERT INTO $TABLE VALUES (cnt); flush privileges; SET cnt = cnt + 1; END WHILE; DROP PREPARE s1; IN_LOOP: LOOP IF cnt >= _nMax THEN LEAVE IN_LOOP; END IF; INSERT INTO $TABLE VALUES (_nVal); flush privileges; DEALLOCATE PREPARE s1; DROP PREPARE s1; SET cnt = cnt + 1; END LOOP IN_LOOP; */ //Variable or condition declaration after cursor or handler declaration //http://blog.naver.com/sylphjm?Redirect=Log&logNo=130035140911 //flush privileges; 를 사용하면. int 5000개에 0.1초가 0.5초 정도로 조금 느려질 수 있다. //http://shapeace.tistory.com/99 //CREATE PROCEDURE p ( IN _vaTableName VARCHAR(20), IN _nMax INT, IN _nVal INT ) $start_time = microtime(true); $TABLE = 'test'; $PROC_QUERY = <<< QUERY_CODE CREATE DEFINER=`root`@`localhost` PROCEDURE p ( _vaTableName VARCHAR(20), _nMax INT, _nVal INT) BEGIN DECLARE max INT DEFAULT 0; DECLARE cnt INT DEFAULT 0; DECLARE str VARCHAR(20); SET cnt = 0; SET str = _vaTableName; WHILE cnt < _nMax DO INSERT INTO $TABLE VALUES (cnt); flush privileges; SET cnt = cnt + 1; END WHILE; COMMIT; END; QUERY_CODE; echo "
"; echo $PROC_QUERY; //'print $text;' Will output the string. Very handy for storing HTML. // $query = "DROP PROCEDURE IF EXISTS p"; $res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); //프로시져 생성 $query = $PROC_QUERY; $res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); $query = "SET @vaTableName = 'test'"; //$res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); $query = "SET @nMax = ". $max_count; //$res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); $query = "SET @nNum = 22"; //$res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); //$query = "CALL p(@vaTableName, @nMax, @nNum)"; $query = "CALL p('test', $max_count, 22)"; $res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); $query = "PREPARE s FROM 'CALL p(@vaTableName, @nMax, @nNum)'"; //$res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); echo "
"; echo "프로시져 생성함"; $query = "EXECUTE s"; //$res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); echo "
"; echo "프로시져 실행함"; $diff_time = microtime(true) - $start_time; echo "
"; echo "진행된 시간 : ". $diff_time. "초"; echo "
"; echo "입력된 갯수 : ". $max_count. "개"; $query = "select count(*) from test where test >= 0"; $result = mysql_query($query); $rs = mysql_fetch_row($result); $count = $rs[0]; echo "총 갯수 : ". $count. "
"; //13.7.5.28. SHOW PROCEDURE CODE Syntax //http://dev.mysql.com/doc/refman/5.6/en/show-procedure-code.html //13.2.5. PROCEDURE INSERT Syntax //http://dev.mysql.com/doc/refman/5.6/en/insert.html //13.2.9. PROCEDURE SELECT Syntax //http://dev.mysql.com/doc/refman/5.6/en/select.html $query = "SHOW PROCEDURE CODE P_InsertTest"; //$res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); echo "이상하게 PROCEDURE에 인자값으로는 INSERT가 실행 되지 않았는데. $변수로는 된다."; //--------------------------------------------------------------------- //쿼리 질의 예제 //--------------------------------------------------------------------- //http://blog.naver.com/arpia2013?Redirect=Log&logNo=30172675497 //include_once ("./lib/dbcon.php"); //$query = "select count(*) from user where uesrid = '$userid'"; //$res = mysql_query($query); //$rs = mysql_fetch_row($res); //$count = $rs[0]; //http://blog.naver.com/miroku0820?Redirect=Log&logNo=100157592741 //$res = mysql_query($query) or die(__FILE__." : Line ".__LINE__."

".mysql_error()); //mysql_close($connect); //$count = mysql_num_rows($query_result); /* for($i=0; $i<$count; $i++) { $num = mysql_result($query_result, $i, num); $name = mysql_result($query_result, $i, num); echo "$num : $name
"; } for($i=0; $i<$count; $i++) { $result_array = mysql_fetch_array($query_result); echo "$result_array[num] : $result_array[name]
"; } */ mysql_close($connect); $total_diff_time = microtime(true) - $total_start_time; echo "
"; echo "TOTAL TIME : ". $total_diff_time; ?>