Archive for May 2013

MySQL: Truncate DB Tables with Constraints

To truncate db Tables with some “linked” data might not be that obvious Task. Foreign Keys always try to interrupt your process.

You can TRUNCATE tables one by one in the correct order. i.e. from “children” to “parents”.
But sometimes this is not possible due to child-parent relations inside the table itself.

so here is a quick tip: Read more


A MySQL TEXT field can store much more data than more often used VARCHARS.
However, sometimes this is not enough, and MySQL starts to truncate those big TEXT streams on insert or update.

According to MySQL 5.6 documentation:

      Type | Maximum length                          | Max UTF-8 chars
  TINYTEXT |           255 ( 2^8 - 1) bytes          |              84
      TEXT |        65,535 (2^16 - 1) bytes = 64 KiB |          21,844
MEDIUMTEXT |    16,777,215 (2^24 - 1) bytes = 16 MiB |       5,592,404
  LONGTEXT | 4,294,967,295 (2^32 - 1) bytes =  4 GiB |   1,431,655,764

UTF-8 characters can require up to 3 bytes per character.
Note that the number of *characters* that can be stored in your column will depend on the *character encoding*.

So, keep in mind, how MANY chars you’d like to store and pick text-field type properly.
And, also, verify the length of the data that you try to store.