MySQL to an Oracle DBA, Part II

Found out some things that you need to be aware of when converting from Oracle to MySQL. Let me create a table and show you some examples.

mysql> create table number_stuff (
    ->    description varchar(20) not null,
    ->    ti tinyint,
    ->    ival integer,
    ->    fl   float,
    ->    dc   decimal(15,5));
Query OK, 0 rows affected (0.13 sec)

This is kind of cool. You can insert multiple values in one shot.

mysql> insert into number_stuff values
    ->    ('123',123, 123, 123, 123),
    ->    ('384',384, 384, 384, 384),
    ->    ('12.5', 12.5, 12.5, 12.5, 12.5),
    ->    ('13.5', 13.5, 13.5, 13.5, 13.5),
    ->    ('14.5', 14.5, 14.5, 14.5, 14.5),
    ->    ('12345.67890',12345.67890, 12345.67890, 12345.67890, 12345.67890);
Query OK, 6 rows affected, 2 warnings (1.78 sec)
Records: 6  Duplicates: 0  Warnings: 2

mysql> show warnings
    -> ;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'ti' at row 2 |
| Warning | 1264 | Out of range value adjusted for column 'ti' at row 6 |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

Fair enough. We tried to insert values outside the bounds (-127 to 127) for TINYINT, and it makes sense that they’d fail. So let’s see what we got here:

mysql> select * from number_Stuff;
+-------------+------+-------+---------+-------------+
| description | ti   | ival  | fl      | dc          |
+-------------+------+-------+---------+-------------+
| 123         |  123 |   123 |     123 |   123.00000 |
| 384         |  127 |   384 |     384 |   384.00000 |
| 12.5        |   13 |    13 |    12.5 |    12.50000 |
| 13.5        |   14 |    14 |    13.5 |    13.50000 |
| 14.5        |   15 |    15 |    14.5 |    14.50000 |
| 12345.67890 |  127 | 12346 | 12345.7 | 12345.67890 |
+-------------+------+-------+---------+-------------+
6 rows in set (0.00 sec)

Our first row looks like it went in just fine.

The second row is what makes the DBA in me a little nervous. We tried to insert the integer value 384 into a TINYINT column and MySQL correctly gave us a warning. However, it then modified our data and put a 127 (the maximum value) into the TINYINT field.

Rows 3, 4, and 5 appear to have gone in without a hitch. But wait, it looks like MySQL insert the rounded number into the columns that contained integers. Hold on, you’re being paranoid, Oracle does the same thing:

SQL> create table xyz (x integer);

Table created.

SQL> insert into xyz (x) values (12.5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

         X
----------
        13

But what am I trying to show you with these numbers? Just that FLOAT and NUMBER round differently:

mysql> select description, round(fl), round(dc) from number_stuf
    -> where description in ('12.5','13.5', '14.5')
    -> ;
+-------------+-----------+-----------+
| description | round(fl) | round(dc) |
+-------------+-----------+-----------+
| 12.5        |        12 |        13 |
| 13.5        |        13 |        14 |
| 14.5        |        14 |        15 |
+-------------+-----------+-----------+
3 rows in set (0.00 sec)

Last, but not least, lets look at real numbers.

mysql> select description, fl, dc
    -> from number_stuff
    -> where description = '12345.67890';
+-------------+---------+-------------+
| description | fl      | dc          |
+-------------+---------+-------------+
| 12345.67890 | 12345.7 | 12345.67890 |
+-------------+---------+-------------+
1 row in set (0.00 sec)

Is that fl a display issue or a storage issue? Lets just use the TRUNCATE function to see what MySQL is really storing:

mysql> select description, fl, truncate(fl, 25) trunc_fl, dc
    -> from number_stuff
    -> where description = '12345.67890';
+-------------+---------+---------------------------------+-------------+
| description | fl      | trunc_fl                        | dc          |
+-------------+---------+---------------------------------+-------------+
| 12345.67890 | 12345.7 | 12345.6787109375000000000000000 | 12345.67890 |
+-------------+---------+---------------------------------+-------------+
1 row in set (0.00 sec)

Yup, that’s really what MySQL is storing, a wrong value. But does it really? Lets multiply by 10000 to see:

mysql> select description, fl, fl * 10000 big_fl, dc
    -> from number_stuff
    -> where description = '12345.67890';
+-------------+---------+-----------------+-------------+
| description | fl      | big_fl          | dc          |
+-------------+---------+-----------------+-------------+
| 12345.67890 | 12345.7 | 123456787.10938 | 12345.67890 |
+-------------+---------+-----------------+-------------+
1 row in set (0.00 sec)

Sure enough, that’s the value being stored.

Note to self: Don’t use FLOAT to store money or fractional shares.

As we were going through data types, I couldn’t understand why there were all these different INTEGER subtypes. The reason is that MySQL will take the entire 4 bytes in a 4 byte INTEGER even though it might not need it. For example, you create the table below:

CREATE TABLE xyz (
   id   BIGINT);

BIGINT is a data type that is an 8 byte integer. You intend to fill xyz.id with sequential numbers starting at 1. When you insert the value 1 into xyz.id, you have taken 8 bytes. This is different than Oracle in that the number data types are variable length. Is this good or bad, depends. It’s just something to be aware of.