我正在尝试选择actor_id为质数的所有演员。数字2和3也是质数。除了我的查询之外，还有其他方法吗？

Question

有没有办法将1、2和3包含在评估actor_id为质数或非质数的过程中？

SELECT * 
FROM actor 
WHERE actor_id > 1 
AND mod(actor_id, 2) != 0 
AND mod(actor_id, 3) != 0 
OR actor_id = 2 or 
actor_id = 3 
ORDER BY actor_id; 

Answer 1

您的查询不起作用，它将返回非质数，例如25。检查过于简单化。你需要检查比2和3多得多的数字。如果你将5加到你的查询中，那么它会删除25，35等，但是你会得到49，所以你需要加上7，以此类推(你可能会发现这里的模式- 2，3，5，7……)所以你需要不断地把素数加到列表中。

与手动构建此查询相比，引用一个包含所有数字的numbers表会更容易。所以你可以使用类似这样的东西：

SELECT  a.actor_id
FROM    actor AS a
WHERE   NOT EXISTS 
        (   SELECT 1 
            FROM   numbers AS n 
            WHERE  n.number > 1 
            AND    n.number <= SQRT(a.actor_id)
            AND    a.actor_id % n.number = 0 
            AND    n.number != a.actor_id
        );

Answer 2

您可以使用SymPy，一个用于符号数学的Python库，来生成小于max('actor_id')的所有素数，并将它们存储在一个表中，如下所示：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Script to create a table containing prime numbers up to the specified maximum value
"""
MAXIMUM_VALUE = 1000
import mariadb
from sympy import *
conn = mariadb.connect(
       user="halley",
       password="XXX",
       host="localhost",
       database="halley")
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS tbl_prime_number (prime_number INTEGER PRIMARY KEY);")
cursor.execute("TRUNCATE TABLE tbl_prime_number")
for primeNumber in sieve.primerange(MAXIMUM_VALUE):
    cursor.execute("INSERT INTO tbl_prime_number (prime_number) VALUES (%(primeNumber)s)", {'primeNumber': primeNumber})
conn.commit()
cursor.execute("SELECT * FROM tbl_prime_number")
result = cursor.fetchall()
for x in result:
    print(x)
cursor.close()
conn.close()

最后，您可以执行连接这两个表的查询：

MariaDB [halley]> SELECT * FROM actor INNER JOIN tbl_prime_number ON actor_id = prime_number;
+----------+------------+--------------+
| actor_id | actor_name | prime_number |
+----------+------------+--------------+
|        2 | Actor 2    |            2 |
|        3 | Actor 3    |            3 |
|        5 | Actor 5    |            5 |
|        7 | Actor 7    |            7 |
|       11 | Actor 11   |           11 |
|       13 | Actor 13   |           13 |
|       17 | Actor 17   |           17 |
...
|      991 | Actor 991  |          991 |
|      997 | Actor 997  |          997 |
+----------+------------+--------------+
168 rows in set (0.001 sec)

PS:我用MariaDB代替了MySQL，但它是一样的。