MySQL高效遍历_mysql处理百万数据遍历速度提升（遍历图片名字是否存在）

CREATE DEFINER=`root`@`localhost` FUNCTION `fun_wcmappendix02`(image_name VARCHAR(50)) RETURNS int(11)

BEGIN

DECLARE sTemp LONGTEXT;

DECLARE sTempChd LONGTEXT; ##4g

SELECT GROUP_CONCAT(t.APPFILE) into sTemp from wcmappendix t where APPFILE not like 'http%';

SELECT GROUP_CONCAT(img) into sTempChd from test2 where img not like 'http%';

if FIND_IN_SET(image_name,sTemp)>0 OR FIND_IN_SET(image_name,sTempChd)>0 THEN

RETURN 1;

ELSE

RETURN 0;

end IF;

END

调用

package test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Types;

import com.mysql.jdbc.CallableStatement;

public class deleteImage2 {

public static void main(String[] args) {

Connection connection = null;

//用于执行 SQL 存储过程的接口

try {

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/gonganbao?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&tcpRcvBuf=1024000";

String user = "root";

String password = "123123";

connection = DriverManager.getConnection(url, user, password);

String sql = "{?=call fun_wcmappendix02(?)}";

//调用存储过程

CallableStatement cstmt = (CallableStatement) connection.prepareCall(sql);

cstmt.registerOutParameter(1,Types.INTEGER);

cstmt.setString(2,"W020180109595367694267.jpg");

cstmt.execute();

int value = cstmt.getInt(1);

System.out.println(value);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}