Tag Archive for mysql

regexp: ADD CONSTRAINT to DROP FOREIGN KEY

if you have something like

ALTER TABLE `accounts_purchases`
  ADD CONSTRAINT `accounts_purchases_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `accounts_purchases_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

and you are about to update those constraints:
You have to drop them first, by getting all their “symbols”…

here is a “simple” regexp to modify SQL export to DROPs Read more

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

MySQL TEXT, MEDIUMTEXT, LONGTEXT, TINYTEXT limits

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 MUCH 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.

MySQL: utf8_unicode_ci vs utf8_general_ci

utf8_general_ci is a very simple collation. What it does – it just
- removes all accents
- then converts to upper case
and uses the code of this sort of “base letter” result letter to compare.
Read more