One of the more frequently asked questions with MySQL 5.7 is “How can I upgrade my JSON data from using TEXT in an earlier version of MySQL to use the native JSON data type?”. Today I wanted to show an example of how to do so, using sample data from SF OpenData.…
After seeing that several blogs discuss storage of UUID values into MySQL, and that this topic is recurrent on forums, I thought I would compile some sensible ideas I have seen, and also add a couple new ones.
Say we have a table of users, and each user has a UUID.…
MySQL is getting native support for JSON. This blog post will show you how to quickly get the MySQL server with these new features running on your Windows rig and how to write a small C# program in Visual Studio 2015 that stores a JSON document using the new native JSON data type.
Schema or Schemaless
The upcoming 5.7 version of MySQL introduces a ton of new features, some of which I am quite excited about—in particular the …[Read more]
The MySQL 5.6.4 release introduced support for
fractional values within the temporal datatypes:
TIMESTAMP. Hence the storage requirement and
encoding differs for them in comparison to older (5.5 and
earlier) temporal datatypes. The storage format for the temporal
datatypes in the old format are not space efficient either, and
recreating tables having both the new and old formats can be a
long and tedious process. For these reasons, we wanted to make it
easier for users to identify precisely which tables, if any, need
to be upgraded.
In my previous blog post, where we looked at the process of upgrading old MySQL-5.5 format temporals to the MySQL-5.6 format, there was the …[Read more]
The temporal datatypes ‘TIME/DATETIME/TIMESTAMP’ supports fractional values starting from MySQL 5.6.4 version. Hence the storage requirement and encoding differs in comparison to pre- MySQL 5.6.4 temporal datatypes.
As mentioned in the section ‘Upgrading from MySQL 5.5 to 5.6′,
Once you have imported the pre-MySQL 5.6.4 tables, ‘ALTER TABLE’ allows creation of tables containing temporal columns of both mysql-5.5 and mysql-5.6 format.
As noted in the documentation, the disadvantages of having such tables are:
- Recreating tables having both 5.5 and 5.6 temporals becomes tedious process when the metadata file(.frm) for the table is unavailable. This would involve recreating the table having the old temporal columns using 5.5 server instance and then adding the 5.6 temporal columns using 5.6 server instance. …
Are bools bools in MySQL - no they're not! Lets show this:
mysql> CREATE TABLE healthcheck ( isworking bool ) ENGINE=MEMORY;Query OK, 0 rows affected (0.14 sec)
mysql> show create table healthcheck\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `healthcheck` (
`isworking` tinyint(1) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
So a 'bool' in MySQL is actually a signed tinyint that has range
-128 to 127. This information is actually hidden away in the
bowels of the MySQL documentation at:http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html"[bool]
These types are synonyms for
TINYINT(1). A value …
SELECT Field0[,Field1,Field2,...] FROM TABLE PROCEDURE ANALYSE() is a nice tool to find out more about your table’s columns. Still, it could be improved in a lot of ways, and the stored procedure below is a starting point. It makes use of procedure analyse (though with ‘SELECT * FROM’), and modifies it’s output to include the […]
No related posts.