WARNING !!!
Before you do anything, mysqldump the entire database or tarball /var/lib/mysql
If you are concerned about case sensitivity, you need to hunt down the usernames in a special way
I tried this little experiment
use test
DROP TABLE rolando;
CREATE TABLE rolando
(id int not null auto_increment,
name varchar(20),primary key (id));
INSERT INTO rolando (name) VALUES
('rolando'),('Rolando'),
('ROLANDO'),('ROLANDO'),
('rolando'),('rolando');
I loaded the sample data
mysql> use test
Database changed
mysql> DROP TABLE rolando;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE rolando
-> (id int not null auto_increment,
-> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO rolando (name) VALUES
-> ('rolando'),('Rolando'),
-> ('ROLANDO'),('ROLANDO'),
-> ('rolando'),('rolando');
Query OK, 6 rows affected (0.08 sec)
Records: 6 Duplicates: 0 Warnings: 0
I ran these queries (please note subtle differences)
mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name | COUNT(1) |
+---------+----------+
| rolando | 6 |
+---------+----------+
1 row in set (0.00 sec)
mysql> SELECT name,MD5(name),COUNT(1) FROM rolando GROUP BY name,MD5(name);
+---------+----------------------------------+----------+
| name | MD5(name) | COUNT(1) |
+---------+----------------------------------+----------+
| ROLANDO | b3f7ba680fe83ab0b5174737e8d536a2 | 2 |
| Rolando | e0732d78dc135b8fcc33ec79bfa89d1f | 1 |
| rolando | e80d4ab77eb18a4ca350157fd487d7e2 | 3 |
+---------+----------------------------------+----------+
3 rows in set (0.00 sec)
mysql> SELECT name,MD5(name)=MD5(LOWER(name)) from rolando;
+---------+----------------------------+
| name | MD5(name)=MD5(LOWER(name)) |
+---------+----------------------------+
| rolando | 1 |
| Rolando | 0 |
| ROLANDO | 0 |
| ROLANDO | 0 |
| rolando | 1 |
| rolando | 1 |
+---------+----------------------------+
6 rows in set (0.00 sec)
mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name | COUNT(1) |
+---------+----------+
| rolando | 6 |
+---------+----------+
1 row in set (0.00 sec)
mysql> SELECT name,(name LIKE lower(name)) like1,
-> (name = binary LOWER(name)) like2 FROM rolando;
+---------+-------+-------+
| name | like1 | like2 |
+---------+-------+-------+
| rolando | 1 | 1 |
| Rolando | 1 | 0 |
| ROLANDO | 1 | 0 |
| ROLANDO | 1 | 0 |
| rolando | 1 | 1 |
| rolando | 1 | 1 |
+---------+-------+-------+
6 rows in set (0.00 sec)
mysql>
Give this view of my experiment, a username with all uppercase or mixed case can be equal to an all lowercase UNLESS YOU USE BINARY
operator with the comparison
I do not think BINARY
can be applied to ON DELETE CASCADE
.
However, you could something like this: To delete all usernames that are lowercase only, try running one of the following:
DELETE FROM logins WHERE username = BINARY LOWER(username);
or
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));
If you run this now, you might nix all usernames in the other tables. Run this instead
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE username = BINARY LOWER(username);
or
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));
You will have to go to all tables that have a username in it and execute
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM <whatevertable1> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable2> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable3> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
.
.
.
DELETE FROM <whatevertableN> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
Since you have a ton of other tables, use the INFORMATION_SCHEMA to script it for you:
DELETE_SQLFILE=/root/SensitiveDelete.sql
MYSQL_CONN="-uroot -ppassword"
echo "SET FOREIGN_KEY_CHECKS=0;" > ${DELETE_SQLFILE}
mysql ${MYSQL_CONN} -ANe"SELECT CONCAT('DELETE FROM ',table_schema,'.',table_name,' WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP ''\^.*[A-Z].+$''\) FROM information_schema.columns WHERE column_name='user_name'" >> ${DELETE_SQLFILE}
cat ${DELETE_SQLFILE}
If the file is correct to you, run it in the mysql client like this
mysql ${MYSQL_CONN} -A < ${DELETE_SQLFILE}
Give it a Try !!!
You can make a table which holds the users with similar taste for every user.
CREATE TABLE `similar_users` (
`uid1` INT,
`uid2` INT,
`score` INT,
PRIMARY KEY (`uid1`, `uid2`)
)
The algorithm for calculating the score can be anything, as long as a higher score means a closer taste.
You you should limit the amount of similar users to a fixed number and also exclude the ones with a score less than a certain value (the ones with a not so similar tastes).
The contents of the table will be calculated periodically (e.g. every day).
Then step 3 from your solution becomes something like this:
SELECT w.film
FROM similar_users su
JOIN wich w ON w.uid = su.uid2 AND w.rate > 2
LEFT JOIN wich wn ON wn.film = w.film AND wn.uid = {current_user}
WHERE su.uid1 = {current_user}
ORDER BY su.score * w.rate DESC
Best Answer
This will not suffer from duplicate votes