Background:
Some time ago, business needs, in order to quickly let the parsing of Excel storage, so the not very sure of the field is set to the text.
Today we need to optimize the table structure, control the field length to the appropriate range, and try not to use the text type.
--Calculation length
Select LENGTH (CAST (fileName as CHAR)) from files;
Select MAX (LENGTH (CAST (fileName as CHAR)) from files;
Select LENGTH (CAST (fileName as CHAR)) as Le from files have le > 20;
--try to convert to varchar type for calculation, but error, perhaps because varchar is longer, it is unreasonable to calculate length on a variable length type.
--This is not very clear, and so later when the more refined time to explain it.
Select LENGTH (CAST (fileName as VARCHAR)) from files;
[Sql]select LENGTH (CAST (fileName as VARCHAR)) from files;
[ERR] 1064-you has an error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' VARCHAR ') from files ' at Line 1
"MySQL" calculates the length of the text type field using the length and cast functions