MySQL converting to TINYINT

Question

Under what circumstances will MySQL convert types to TINYINT? I know that a BOOL or BOOLEAN type in mysql DDL will automatically be converted to TINYINT(1) for for true or false. I am analyzing a database which has a type of varchar(16) on a field in one table, and tinyint(4) on the same field on another table? E.g t1.name varchar(15) and t2.name tinyint(4) where t1.name=t2.name.

Very bad choice for a join condition. The types should be the same. — Gordon Linoff
But could it be that they might be a third unkown table that perhaps joins the table tables indirectly by directly joining to t2.name e.g create table t3(id...PK, ws_connect tinyint(4), name varchar(16));? — The Georgia

Benvorth Benvorth · Accepted Answer · 2014-09-25T11:50:05

Don't rely on implicit type conversion, do your datatype analysis manually:

First lets see what MySQL thinks as the best col-type for your data. Run a

 SELECT * FROM table PROCEDURE Analyse()

Analyse your data further by saying

 SELECT * FROM table WHERE varcharCol NOT REGEXP '^[0-9].*$'

To get all non-numeric values in varcharCol. If there are non you finally have to check value-ranges of different MySQL-types here.

Then you are ready to convert your varcharCol e.g. to TINYINT.

MySQL converting to TINYINT

1 Answers