Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Jul 12, 2005

MySQL Oddities - Page 2

By Ian Gilfillan

More AUTO_INCREMENT oddities

As we have seen above, AUTO_INCREMENT fields take the next value in the sequence as default, even when specifically supplied a NULL value. Here are a few more cases where things are not quite what they seem.

mysql> INSERT INTO t1 (i1) VALUES (0);
Query OK, 1 row affected (0.06 sec)

Has it done what you would expect? Nope - 0 has become 3.

mysql> SELECT * FROM t1;
+----+----+-----------+------+---------------------+
| i1 | i2 | f1        | f2   | ts                  |
+----+----+-----------+------+---------------------+
|  1 |  0 |           | nada | 2005-07-06 21:58:15 |
|  2 |  0 | something | nada | 2005-07-06 21:58:40 |
|  3 |  0 |           | nada | 2005-07-06 22:33:16 |
+----+----+-----------+------+---------------------+

Let's try some more:

mysql> INSERT INTO t1 (i1) VALUES (-1);
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO t1 (i1) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 (i1) VALUES (9999999999999999999999999999);
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> INSERT INTO t1 (i1) VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 1

mysql> SELECT * FROM t1;
+------------+----+-----------+------+---------------------+
| i1         | i2 | f1        | f2   | ts                  |
+------------+----+-----------+------+---------------------+
|          1 |  0 |           | nada | 2005-07-06 21:58:15 |
|          2 |  0 | something | nada | 2005-07-06 21:58:40 |
|          3 |  0 |           | nada | 2005-07-06 22:33:16 |
|         -1 |  0 |           | nada | 2005-07-06 22:34:58 |
|          4 |  0 |           | nada | 2005-07-06 22:35:01 |
| 2147483647 |  0 |           | nada | 2005-07-06 22:42:40 |
+------------+----+-----------+------+---------------------+

5 rows in set (0.00 sec)

MySQL is behaving quite consistently here, but in a surprising way. AUTO_INCREMENT fields, as we saw above, take the next value in the sequence as default if you attempt to insert a NULL value. If you attempt to insert a negative value, or a larger value than the numeric can handle, MySQL's behavior is undefined. Running MySQL 5.0.2, MySQL successfully inserts a negative number (other versions simply insert the next number in the sequence) and the attempt to insert zero is treated the same as if you had inserted a null (so 4 is inserted, the next in the sequence). Entering a number greater than the integer can handle inserts a number equal to the maximum value of the integer (other versions have also simply inserted the next in the sequence). However, as soon as you attempt to insert a valid number (1 in this case), MySQL will attempt to insert this actual number, and, being a primary key, this fails since we already have a record with this value.

Not quite boolean

Since MySQL has no boolean data types, many use the ENUM type, as follows:

mysql> 
CREATE table boolean (
  i1 INT, 
  b1 ENUM('0','1')
)

And that works fine in most cases. However, it is easy to make a few mistakes. Here is a common one:

mysql>
CREATE table boolean2 (
  i1 INT, 
  b1 ENUM('0','1') NOT NULL DEFAULT 1
);

You are probably assuming here that the field b1 defaults to true (1). Don't be too sure.

mysql> INSERT INTO boolean2 (i1) VALUES (1);

mysql> SELECT * FROM boolean2;
+------+------+
| i1   | b1   |
+------+------+
|    1 | 0    |
+------+------+
1 row in set (0.00 sec)

In spite of setting the default to 1, MySQL has entered 0 as a value. What happened? Simple - ENUM is a character type, so the default value should be a string. MySQL however does not warn you of your error. Let's correct that mistake, and try again.

CREATE table boolean3 (
  i1 INT, 
  b1 ENUM('0','1') NOT NULL DEFAULT '1'
);

mysql> INSERT INTO boolean3(i1) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM boolean3;
+------+----+
| i1   | b1 |
+------+----+
|    1 | 1  |
+------+----+

Let's create another version of the table, where the ENUM field still cannot be null, but it has no predefined default.

CREATE table boolean5(
  i1 INT, 
  b1 ENUM('0','1') NOT NULL
);

mysql> INSERT INTO boolean5 (i1) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM boolean5;
+------+----+
| i1   | b1 |
+------+----+
|    1 | 0  |
+------+----+

As with our earlier examples, MySQL inserts a default without warning you. In this case, the default is the first ENUM option, '0'. Another common mistake:

mysql> INSERT INTO boolean5 (b1) VALUES (1);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM boolean5;
+------+----+
| i1   | b1 |
+------+----+
|    1 | 0  |
| NULL | 0  |
+------+----+
2 rows in set (0.00 sec)

We have made the same mistake as before, except this time in inserting the data. We have forgotten that b1 is a string, and our value should have been enclosed in quotes. The field i1 is NULL, as we would expect if we did not define it, as it has been defined to accept NULLs

Conclusion

Most people do not work only in one DBMS, and if you have moved from another DBMS, or plan to work on another in the future, do not let MySQL's oddities catch you by surprise. There are still many more lurking and we will look at some of these in future articles.

» See All Articles by Columnist Ian Gilfillan



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM