On Wed, 27 Aug 2003, Ignatius Reilly wrote: > Read the MySQL manual.
Hmmm, I thought I did. 6.2.2.4: Illegal YEAR values are converted to 0000. > "0" value is interpreted as 2000. > Your empty string is converted to an integer, thus 0. What confuses me (what I can't find) is why a quoted empty string "" is converted to a quoted integer "0" for a YEAR type. Based on the manual, I assumed an empty string is considered an Illegal YEAR and thus converted to 0000. The empty string is NOT converted to an UNQUOTED digit 0, because otherwise the field would be set to 0000 (the intended and expected action). You must specify it as a string '0' or '00' or it will be interpreted as 0000. Can you point me to the correct portion of the manual that explains that? What you explain sounds like MySQL is working as designed, but I'm a bit embarrassed that I missed that in the manual, so I want to read up on it. Seems a bit obscure; I've been using mysql for 4+ years and have never came across this. Thanks, Beckman > ----- Original Message ----- > From: "Peter Beckman" <[EMAIL PROTECTED]> > > > Seems that either I don't understand mysql, or something. > > > > My table, with the non-important things removed... > > > > mysql> explain plate; > > > +---------+-----------------------+------+-----+---------------------+------ > ----------+ > > | Field | Type | Null | Key | Default | > Extra | > > > +---------+-----------------------+------+-----+---------------------+------ > ----------+ > > | pid | mediumint(8) unsigned | | PRI | NULL | > auto_increment | > > | year | year(4) | YES | | NULL | > | > > [...] > > > > So my assumption is that if I insert with year="" it should use the > > default. Or at least 0000. > > > > mysql> update plate set year=NULL where pid=65; > > Query OK, 1 row affected (0.00 sec) > > Rows matched: 1 Changed: 1 Warnings: 0 > > > > mysql> select * from plate where pid=65; > > +-----+---------+------+- > > | pid | plate | year | > > +-----+---------+------+- > > | 65 | DVF0343 | NULL | > > +-----+---------+------+- > > > > But if I do this: > > > > mysql> update plate set year="" where pid=65; > > Query OK, 1 row affected (0.01 sec) > > Rows matched: 1 Changed: 1 Warnings: 1 > > > > mysql> select * from plate where pid=65; > > +-----+---------+------+- > > | pid | plate | year | > > +-----+---------+------+- > > | 65 | DVF0343 | 2000 | > > +-----+---------+------+- > > > > 2000? What? Why? Confused. PHP or Mysql fault? --------------------------------------------------------------------------- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --------------------------------------------------------------------------- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
