Geek Answers Handbook

MySQL checks email records

I know that you can use PHP email validation filters to check if this data is abc @ abc . ext

In any case, I can run a MySQL query for this, check only this style format. Otherwise, I will need to paginate and load a balanced script limit to select all records from the database (50,000+), and the server is already slow for my client on a shared hosting.

I just want to see a list of only valid letters, and also check for duplicates or similarities with the request like?

Rate any collaboration on this.

+5
source share
5

-

SELECT * FROM users WHERE email NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';


SELECT * FROM users WHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$';

Update. , Mysql.

+13

-

SELECT * FROM your_table
WHERE email_col LIKE '%@%.%'

() , , . , , ...
, ...

+9

SQL, mysql. :

CREATE DEFINER=`root`@`localhost` TRIGGER `users_before_insert` BEFORE INSERT ON `users` FOR EACH ROW BEGIN
IF (NEW.`email` REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')=0  THEN
        SIGNAL SQLSTATE VALUE '45000'
            SET MESSAGE_TEXT = '[table:users] - `email` incorrect format!';
    END IF;
END
+1

, , "@" ".". . .

SELECT * FROM school email REGEXP "^ [A-Za-Z0-9] [A-Za-Z0-9.-] * [A-Za-Z0-9.-] @[A-Za-Z0-9] [A-Za-Z0 -9._-] * [A-Za-Z0-9]\[A-Za-Z] {2,4} $';.

0

Same as @Oyeme, but I need to change \. by [\.] and I don’t know why. If not test @test, com is still considered valid ...

SELECT * FROM users WHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+[\.][A-Z]{2,4}$';

PS: I can not comment on his answer because of the reputation

0
source

More articles:


All Articles