How do you select all rows which match one individual row in either of a thousand columns?

For example, my personality match database has 1000 columns, with genre titles such as:

autoid | movie_genre_comedy | movie_genre_action | movie_genre_horror | more genres --> 
23432  | 1                  | 0                  | 1                  | 0
3241   | 0                  | 1                  | 1                  | 0
64323  | 0                  | 1                  | 0                  | 0

How do I match every row to the row with autoid 23432 so that the following table is produced:

autoid | movie_genre_comedy | movie_genre_action | movie_genre_horror | more genres --> 
23432  | 1                  | 0                  | 1                  | 0
3241   | 0                  | 1                  | 1          开发者_StackOverflow社区        | 

Note that the row with autoid 64323 is not there because it does not have any similar columns to the chosen row with autoid 23432.

The simplest way to do this is:

SELECT *
from genretable
WHERE movie_genre_comedy = 1 
OR movie_genre_horror = 1 
OR ........... and so on for up to 1000 parameters. 

---

The code you mentioned in your question is really the only way to do what you want with your current table structure. The answer is to create two new tables to map users to personality traits, like so:

create table `personality_trait_values`
(
     `id` smallint auto_increment primary key
    ,`value` varchar(20) not null unique
);

create table `personality_traits`
(
     `user_id` int not null references `users` (`autoid`)
    ,`personality_trait_id` int not null references `personality_trait_values` (`id`)
    ,unique (`user_id`,`personality_trait_id`)
);

With that, you can nuke the 1000 columns that describe whether the user has a personality trait or not, and your query becomes much more compact:

select u.`autoid`
    from `personality_traits` pt1
        join `personality_traits` pt2
            on pt1.`personality_trait_id` = pt2.`personality_trait_id`
            and pt1.`user_id` != pt2.`user_id`
    where pt1.`user_id` = `v_user_id_to_compare_to`

Where v_user_id_to_compare_to is a variable you have set previously in your stored procedure (to 23432, in the case of your question).

Converting the table structure you have now will be a bit tedious, but well worth it, and a lot of the tedium can be relieved by judicious use of copy/paste.