Showing entries 1 to 7
Displaying posts with tag: datatypes (reset)
Upgrading JSON data stored in TEXT columns

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

Storing UUID Values in MySQL Tables

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.

Different techniques

Say we have a table of users, and each user has a UUID.…

Getting Started With MySQL & JSON on Windows

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]
How to Easily Identify Tables With Temporal Types in Old Format!

The MySQL 5.6.4 release introduced support for fractional values within the temporal datatypes: TIME, DATETIME, and 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]
Upgrading old MySQL-5.5 format temporals to MySQL-5.6 format.

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′,

http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

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.
[Read more]
Are bools bools in MySQL?

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 ***************************

Table: healthcheck

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 …

[Read more]
Making use of procedure analyse()

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.

Showing entries 1 to 7