用mysql写三种方法 定义函数一个函数 并输出n以内所有的素数

方法一：使用存储过程

DELIMITER $$
CREATE PROCEDURE find_primes(n INT)
BEGIN
    DECLARE i INT DEFAULT 2;
    DECLARE is_prime BOOLEAN;
    WHILE i <= n DO
        SET is_prime = TRUE;
        DECLARE j INT DEFAULT 2;
        WHILE j <= SQRT(i) DO
            IF i % j = 0 THEN
                SET is_prime = FALSE;
                LEAVE WHILE;
            END IF;
            SET j = j + 1;
        END WHILE;
        IF is_prime THEN
            SELECT i;
        END IF;
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL find_primes(100);

方法二：使用自定义函数

DELIMITER $$
CREATE FUNCTION is_prime(num INT) RETURNS BOOLEAN
BEGIN
    DECLARE i INT DEFAULT 2;
    DECLARE sqrt_num INT DEFAULT SQRT(num);
    WHILE i <= sqrt_num DO
        IF num % i = 0 THEN
            RETURN FALSE;
        END IF;
        SET i = i + 1;
    END WHILE;
    RETURN TRUE;
END$$
DELIMITER ;

SELECT * FROM (
    SELECT 2 AS num
    UNION ALL
    SELECT num + 1 FROM (
        SELECT num FROM (
            SELECT num FROM (
                SELECT 2 AS num
                UNION ALL
                SELECT num + 1 FROM (
                    SELECT num FROM (
                        SELECT num FROM (
                            SELECT 2 AS num
                            UNION ALL
                            SELECT num + 1 FROM (
                                SELECT num FROM (
                                    SELECT 2 AS num
                                    UNION ALL
                                    SELECT 1 AS num
                                ) AS a
                            ) AS b
                            LIMIT 97
                        ) AS c
                    ) AS d
                ) AS e
            ) AS f
        ) AS g
    ) AS h WHERE is_prime(num + 1)
) AS i;

方法三：使用临时表

CREATE TEMPORARY TABLE primes (
    num INT PRIMARY KEY
);

INSERT INTO primes (num)
VALUES (2);

SET @i = 3, @n = 100;

WHILE @i <= @n DO
    INSERT INTO primes (num)
    SELECT @i
    FROM primes
    WHERE NOT EXISTS (
        SELECT *
        FROM primes AS p
        WHERE p.num < SQRT(@i) AND @i % p.num = 0
    )
    LIMIT 1;
    SET @i = @i + 1;
END WHILE;

SELECT * FROM primes;