Mysql´æ´¢¹ý³Ì£¨Èý£©——´¦Àí·ÖÒ³

·ÖÒ³¹¦ÄÜÔÚÈκÎÓ¦ÓÃÖж¼±È½Ï³£¼û£¬¶ø
Mysql
µÄ´æ´¢¹ý³Ì·ÖÒ³±ØÐëͨ¹ý¶¯Ì¬
sql
À´Ö´ÐС£·ÖÒ³¶ÔÓ¦µÄ
offset
ºÍ
row_count
±ØÐëÏÈÓÃ
concat
º¯Êý±ä³É×Ö·û´®×é×°µ½
sql
ÖУ¨ÈçÓï¾ä
1
£©£¬¶ø²»ÄÜÖ±½ÓʹÓã¨ÈçÓï¾ä
2
£¬
Mysql
²»Ö§³Ö£©¡£
Mysql
·ÖÒ³¹¦ÄܵÄʵÏÖÈçÏ´úÂëËùʾ£º
        
Sql
code 3-1:
                  
CREATE PROCEDURE test_proc_multi_select(IN i_pageIndex INT, IN i_pageSize INT)
BEGIN
declare stmt varchar(2000);
set @sql = concat('select * from testproc limit ',(i_pageIndex-1) * i_pageSize,' , ',i_pageSize); -- Óï¾ä1×é×°sql
prepare stmt from @sql; -- µÃµ½prepare stmt
execute stmt; -- Ö´ÐÐselect
-- ////ÒÔÏ·½Ê½±àÒë²»ÄÜͨ¹ý£¡
-- select * from testproc limit (i_pageIndex-1)*i_pageSize,i_pageSize; -- Óï¾ä2
END
                  
ÒÔÏ´úÂëÌṩÁË´ø
where
Ìõ¼þµÄ·ÖÒ³£º
        
Sql
code 3-2:
CREATE PROCEDURE test_proc_param_select(IN i_name VARCHAR(100),IN i_pageIndex INT, IN i_pageSize INT)
BEGIN
declare stmt varchar(2000);
set @sql = concat('select * from testproc where name like ''%',i_name,'%'' limit ',(i_pageIndex-1) * i_pageSize,',',i_pageSize); --×¢ÒâlikeºóÁ½¸öµ¥ÒýºÅ±íʾһ¸ö¡£
prepare stmt from @sql;
execute stmt;
END;