".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("");
}
}
*/
//¸ðµÎ »èÁ¦
$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;
?>