动态平局分配节点 Mysql 存储过程

替换 your_table
sql：

BEGIN

# 节点分布情况(没下架的)
DROP VIEW
IF EXISTS `view_node_count`;

# 创建一个视图
CREATE ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER 
VIEW `view_node_count` AS ( 
        select
            count(0) as c_num, node 
        from 
            your_table
        where `status` <> 10 and `node` <> '33_2' and node <> '33_3' GROUP BY node
);

# count最多的那台机器的数量
set @max_count=                             
(
    select MAX(c_num)
    from view_node_count
);

# count最少的那台机器的数量
set @min_count=                             
(
    select MIN(c_num)
    from view_node_count
);

set @max_node = (select node from view_node_count where c_num = @max_count limit 1);

set @min_node = (select node from view_node_count where c_num = @min_count limit 1);

# 更新最大node的到最小的上
update your_table
set node = @min_node
where node = @max_node and status <> 10 LIMIT 1;

SELECT * from view_node_count; # 显示结果
END