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
– Gordon Linoff, Commented Sep 9, 2014 at 5:37
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
– The Georgia, Commented Sep 9, 2014 at 5:47

Benvorth · Accepted Answer · 2014-09-25 11:50:05Z

1

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.

answered Sep 25, 2014 at 11:50

Benvorth

7,8028 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL converting to TINYINT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related