MySQL - History Tables

Keeping a history of your data can be immensely useful, such as for reverting silly mistakes, or for auditing purposes. This tutorial will show you a really simple way to achieve this in a generic manner that can be applied to any table. You will be able to see what…

Read this article

MySQL Decimal

The decimal type stores an exact value, which makes it appropriate for storing currency, unlike the float and double types. Unlike integer types, the storage requirements vary, with the value being stored within the database in binary format. Definition When defining a decimal, one specifies the number of digits in…

Read this article

MySQL - Foreign Keys

Foreign keys let you cross-reference related data across tables. This is primarily an excellent tool to maintain data integrity, but it can also help the query optimizer with complex queries. This relationship is always a parent -> child relationship where the foreign key is defined in the child table definition.…

Read this article

MySQL Integer Types

Integer Types and Ranges TINYINT Unsigned Range 0 - 255 Signed Range -128 - 127 Storage Space: 1 byte SMALLINT Unsigned Range 0 - 65535 Signed Range -32768 - 32767 Storage Space: 2 bytes MEDIUMINT Unsigned Range 0 - 16777215 Signed Range -8388608 - 8388607 Storage Space: 3 bytes INT…

Read this article

MySQL Enums

Enums can be a great way to store a value for a column when there are only a finite number of possible string values. A common example for this could be the status for a support ticket. These could be open, assigned, resolved, and closed. Another example could be the…

Read this article

Solving MySQL InnoDB Corruption

Today I got a scary error message when running some database migration scripts: mysqli::query(): (HY000/1712): Index countermeasure_costs is corrupted My first reaction was to just restore an earlier snapshot and run the scripts again. Unfortunately this happened again, so I ran the restoration process again and checked…

Read this article

CentOS - Install MySQL Server

CentOS does not come with MySQL in the repositories. Instead it has switched over to MariaDB. The script below will install MariaDB 5.5 which is extremely similar to MySql 5.5. sudo yum install mariadb-server -y sudo systemctl start mariadb sudo systemctl enable mariadb mysql_secure_installation Now you…

Read this article

Debian 8 - Install MySQL 5.7

This is exactly the same as installing MySQL 5.7 for ubuntu, so please follow it's steps. This will result in installing MySQL 5.7.11 when this post was written.…

Read this article

What's New In MySQL 5.7

The official documentation about what has changed in 5.7, but I'll summarize the most exciting changes below: JSON Support Beginning with MySQL 5.7.8, MySQL supports a native JSON type. JSON values are not stored as strings, but are stored using an internal binary format that permits quick…

Read this article

MySQL 5.7 New JSON Functionality

Beginning with MySQL 5.7.8, MySQL supports a native JSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements. JSON documents stored in JSON columns are automatically validated whenever they are inserted or updated, with an…

Read this article