MySQL遍歷數據表是我們經常會遇到的問題,下面就為您詳細介紹MySQL遍歷數據表的方法,希望對您學習MySQL遍歷數據表能有所幫助。
- DELIMITER $$
- USE `tns`$$
- DROP PROCEDURE IF EXISTS `MakeFriends`$$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `MakeFriends`()
- BEGIN
- DECLARE v_adminId BIGINT(12);
- DECLARE v_currentUserID BIGINT(12);
- DECLARE v_ExistRela INT;
- DECLARE Done INT DEFAULT 0;
- DECLARE rs CURSOR FOR SELECT userId FROM hw_user;
- /* 異常處理 */
- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
- SET v_adminId = 1;
- /* 打開游標 */
- OPEN rs;
- /* 逐個取出當前記錄userId字段的值*/
- FETCH NEXT FROM rs INTO v_currentUserID;
- /* 遍歷數據表 */
- REPEAT
- IF NOT Done THEN
- SELECT COUNT(*) FROM hw_relationship WHERE sourceId = v_currentUserID AND targetId = v_adminId INTO v_ExistRela;
- /*SELECT v_currentUserID;SELECT v_ExistRela;*/
- IF v_currentUserID != v_adminId AND v_ExistRela = 0 THEN
- /* insert relation records*/
- INSERT INTO hw_relationship(createTime, relationType, sourceId, targetId) VALUES (NOW(), 1, v_currentUserID, v_adminId);
- INSERT INTO hw_relationship(createTime, relationType, sourceId, targetId) VALUES (NOW(), 1, v_adminId, v_currentUserID);
- END IF;
- END IF;
- FETCH NEXT FROM rs INTO v_currentUserID;
- UNTIL Done END REPEAT;
- /* 關閉游標 */
- CLOSE rs;
- END$$
- DELIMITER ;
MySQL批量導入數據的實現
php mysql創建臨時表
mysql快速建表的方法
深入研究MySQL刪除多表數據
MySQL大表備份的簡單方法