2

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.

The Georgia
  • 1,005
  • 7
  • 23
  • 59
  • 1
    Very bad choice for a `join` condition. The types should be the same. – Gordon Linoff Sep 09 '14 at 05: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 Sep 09 '14 at 05:47

1 Answers1

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.

Benvorth
  • 7,416
  • 8
  • 49
  • 70