I was all set to tell you why 16.125 became 16.12 when I ran the test on our
production box. It looks like some where between 4.0.20 and 5.0.4 the ALTER
TABLE to a decimal data type changed from truncation to rounding.
Redhat MySQL 4.0.20 truncates all
Windows XP MySQL 5.0.4 Rounds with Windows algorithm
I suspect a MySQL 5.0.4 on Linux would round with Linux alorithm but I don't
have one to test.
See following:
The decimal(6,2) tells MySQL to "round" all values to 2 places and store the
results in a decimal field. MySQL uses the Round routines of the host.
On a Windows box
16.125 = 16.13
16.135 = 16.14
On a Linux/Unix box
16.125 = 16.12
16.135 = 16.14
Here it is on windows
____________________________________________________________________________
mysql> select * from dcml;
+--------+
| a |
+--------+
| 16.00 |
| 16.25 |
| 16.125 |
| 16.135 |
+--------+
4 rows in set (0.00 sec)
mysql> alter table dcml modify a decimal(6,2);
Query OK, 4 rows affected, 2 warnings (0.28 sec)
Records: 4 Duplicates: 0 Warnings: 2
mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------+
| Note | 1265 | Data truncated for column 'a' at row 3 |
| Note | 1265 | Data truncated for column 'a' at row 4 |
+-------+------+----------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from dcml;
+-------+
| a |
+-------+
| 16.00 |
| 16.25 |
| 16.13 |
| 16.14 |
+-------+
4 rows in set (0.00 sec)
_________________________________________________________________________
And on Linux
_________________________________________________________________________
mysql> create table dcml (a varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dcml values('16.00'),('16.25'),('16.125'),('16.135');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from dcml;
+--------+
| a |
+--------+
| 16.00 |
| 16.25 |
| 16.125 |
| 16.135 |
+--------+
4 rows in set (0.00 sec)
mysql> alter table dcml modify a decimal(6,2);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 2
mysql> show warnings;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corre
Our production system is on 4.0.20 which does not support SHOW WARNINGS
and apparently the ALTER truncates always
mysql> select * from dcml;
+-------+
| a |
+-------+
| 16.00 |
| 16.25 |
| 16.12 |
| 16.13 |
+-------+
4 rows in set (0.00 sec)
_________________________________________________________________________
Linux rule paraphrased: If the value to the right of the rounding column is
a 5 then if the rounding column is even round down if the rounding column is
odd round up.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 18, 2005 10:00 AM
To: Jerry Swanson
Cc: mysql@lists.mysql.com
Subject: Re: varchar(10) to decimal
Hi,
if varchar represents decimal(6,x) where x>2, it's truncated. Else, it's
converted :
mysql> create table dcml (a varchar(10));
Query OK, 0 rows affected (0.24 sec)
mysql> insert into dcml values('16.00'),('16.25'),('16.125'),('16.135');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from dcml;
+--------+
| a |
+--------+
| 16.00 |
| 16.25 |
| 16.125 |
+--------+
3 rows in set (0.03 sec)
mysql> alter table dcml modify a decimal(6,2);
Query OK, 3 rows affected, 1 warning (0.24 sec)
Records: 3 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 3 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> select * from dcml;
+-------+
| a |
+-------+
| 16.00 |
| 16.25 |
| 16.12 |
+-------+
3 rows in set (0.00 sec)
Here, only row 3 is truncated !
Mathias
Selon Jerry Swanson <[EMAIL PROTECTED]>:
> decimal(6,2)
>
> On 5/18/05, Philippe Poelvoorde <[EMAIL PROTECTED]> wrote:
> > Jerry Swanson wrote:
> >
> > > I need to change format from varchar(10) to decimal.
> > > When I alter the table the data is trimmed.
> > >
> > > What I'm doing wrrong?
> > >
> > > TH
> > >
> >
> > ALTER TABLE your_table MODIFY your_column double NOT NULL DEFAULT '0.0';
> > should normally work. What is the command you are doing and have you
> > example results ?
> > How did you declare your decimal column ?
> >
> > --
> > Philippe Poelvoorde
> > COS Trading Ltd.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
