I have a table products
where the column code
is a TINYTEXT
.
When trying to create a foreign key ( FOREING KEY
) I get this error:
#1170 - BLOB column 'code' column used in key specification without key size
Since you can't create foreign keys using fieldstinytext
( among others ), I decided to convert the column code
to VARCHAR
, but, to correctly define the length of VARCHAR
I need to know:
How many characters has the longest code stored in the table?
I was able to get the result using
CHAR_LENGTH
.Solution:
Explanation:
With
SELECT CHAR_LENGTH(code) AS codeLength
I get the number of characters thatcode
each product has.By adding
ORDER BY codeLength DESC
, I get the products ordered by their character length from highest to lowest, that is, those with the code with the most characters first .And finally with
LIMIT 1
, I get only the first one.