How to join in SQL on a PHP serialized primary key

Question

Ok this is weird, I know. I just want ideas about how to deal with it.

I want to do a join

---   ---
 A     B
---   ---
id    id
idB
---   ---

A standard join would be :

SELECT *
FROM B
JOIN A ON A.idB = B.id
WHERE A.id = 3

But here, my idB is not an int foreign key, it is PHP serialized.

For example, instead of being 5, it is "int(5)".

Is there a way to do the join (is there a SQL "serialize" function ?), like that :

SELECT *
FROM B
JOIN A ON A.idB = SERIALIZE(B.id)
WHERE A.id = 3

Important : I can't know the type of the foreign key. Here it is "int(5)", but it can also be a string, an array...

Answer 1

The PHP serialization format is something only PHP understands and it's totally unrelated to SQL. You just cannot do anything in the database server side: you do not have information stored in the database in a usable format. It's just like trying to search for words in the picture of a scanned document.

You could add a new A.unserialized_b_id column and run a PHP script to feed it with an initial data load. After that, new inserts should be required to update the column themselves.

However, you say that it can be any sort of data type. You have to ask yourself whether it makes sense to perform a join when the ID is not a scalar value. If the answer is no, then just make your PHP insert a NULL in such cases. If the answer is yes, then it's going to be fun :)