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

方法一：使用存储过程

DELIMITER $$
CREATE PROCEDURE `get_prime_numbers`(IN `n` INT)
BEGIN
    DECLARE i INT DEFAULT 2;
    DECLARE j INT;
    WHILE i <= n DO
        SET j = 2;
        WHILE j <= i DO
            IF i % j = 0 AND i != j THEN
                LEAVE inner_loop;
            END IF;
            SET j = j + 1;
        END WHILE;
        SELECT i AS prime_number;
        inner_loop: SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

然后调用存储过程即可：

CALL get_prime_numbers(100);

方法二：使用函数

DELIMITER $$
CREATE FUNCTION `is_prime_number`(`n` INT) RETURNS INT
BEGIN
    DECLARE i INT DEFAULT 2;
    WHILE i <= n / 2 DO
        IF n % i = 0 THEN
            RETURN 0;
        END IF;
        SET i = i + 1;
    END WHILE;
    RETURN 1;
END$$

CREATE FUNCTION `get_prime_numbers`(`n` INT) RETURNS TEXT
BEGIN
    DECLARE i INT DEFAULT 2;
    DECLARE result TEXT DEFAULT '';
    WHILE i <= n DO
        IF is_prime_number(i) THEN
            SET result = CONCAT(result, i, ',');
        END IF;
        SET i = i + 1;
    END WHILE;
    RETURN result;
END$$
DELIMITER ;

然后调用函数即可：

SELECT get_prime_numbers(100);

方法三：使用触发器

DELIMITER $$
CREATE TABLE `prime_numbers` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `number` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci$$

DELIMITER $$
CREATE TRIGGER `insert_prime_number` BEFORE INSERT ON `prime_numbers`
FOR EACH ROW
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM `prime_numbers` WHERE `number` = NEW.`number`
    ) AND NEW.`number` > 1 AND (
        NEW.`number` = 2 OR NEW.`number` % 2 != 0
    ) THEN
        DECLARE i INT DEFAULT 2;
        WHILE i <= NEW.`number` / 2 DO
            IF NEW.`number` % i = 0 THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not a prime number';
            END IF;
            SET i = i + 1;
        END WHILE;
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not a valid number';
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `get_prime_numbers`(IN `n` INT)
BEGIN
    DECLARE i INT DEFAULT 2;
    WHILE i <= n DO
        INSERT INTO `prime_numbers`(`number`) VALUES(i);
        SET i = i + 1;
    END WHILE;
    SELECT `number` AS prime_number FROM `prime_numbers`;
END$$
DELIMITER ;

然后调用存储过程即可：

CALL get_prime_numbers(100);