I'm trying to select all the actors whose actor_id is a prime number. And number 2 and 3 is als-CodePudding

Is there a way to include the 1, 2, and 3 in evaluating the actor_id as prime or not?

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;

CodePudding user response：

Your query doesn't work, it will return non-prime numbers, such as 25. The check is too simplistic. You need to check against a lot more numbers than just 2 and 3. If you add 5 to your query, then it removes 25, 35, etc, but you then get 49 returned, so you need to add 7, and so on and so on (you may spot the pattern here though - 2, 3, 5, 7...) so you need to keep adding prime numbers to the list.

Rather than manually building this query, it would be easier to reference a numbers table with all numbers. So you could use something like this:

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
        );

Example on DB Fiddle

CodePudding user response：

You can use SymPy, a Python library for symbolic mathematics, to generate all prime numbers less than max('actor_id'), the MAXIMUM_VALUE, and store them in a table, as shown below:

#!/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()

Finally you can execute a query joining the two tables:

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: I used MariaDB instead of MySQL, but it is the same.