>From http://www.mysql.com/doc/N/u/Numeric_types.html
The NUMERIC and DECIMAL types are implemented as the same type by MySQL, as
permitted by the SQL92 standard. They are used for values for which it is
important to preserve exact precision, for example with monetary data. When
declaring a column of one of these types the precision and scale can be (and
usually is) specified; for example:
salary DECIMAL(9,2)
In this example, 9 (precision) represents the number of significant decimal
digits that will be stored for values, and 2 (scale) represents the number
of digits that will be stored following the decimal point. In this case,
therefore, the range of values that can be stored in the salary column is
from -9999999.99 to 9999999.99. (MySQL can actually store numbers up to
9999999.99 in this column because it doesn't have to store the sign for
positive numbers)
First I am not sure if you intended 9999999.99 to be 99999999.99 in "(MySQL
can actually store numbers up to 9999999.99 in this column because it
doesn't have to store the sign for positive numbers)"
If so then I do not think this is correct. I think that decimal(9,2) means
there is an optional sign character, up to 7 whole digits, a decimal point
and up to two decimal digits. I think the whole parenthetical statement
should be removed.
Red Hat Linux 7.1 2.96-85
mysql-3.23.42 (Official MySQL RPM)
Configure command: ./configure --disable-shared
--with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static
--without-berkeley-db --without-innodb --enable-assembler
--with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock
--prefix=/ --with-extra-charsets=complex --exec-prefix=/usr
--libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share
--localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include
--mandir=/usr/man '--with-comment=Official MySQL RPM'
This shows the problem:
mysql -p dickensn
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 3.23.42
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table t1 (num1 numeric(9,2));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (1234567.12);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------------+
| num1 |
+------------+
| 1234567.12 |
+------------+
1 row in set (0.00 sec)
mysql> update t1 set num1=12345678.12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+-------------+
| num1 |
+-------------+
| 12345678.12 |
+-------------+
1 row in set (0.00 sec)
mysql> update t1 set num1=-1234567.12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+-------------+
| num1 |
+-------------+
| -1234567.12 |
+-------------+
1 row in set (0.00 sec)
mysql> update t1 set num1=-12345678.12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> select * from t1;
+-------------+
| num1 |
+-------------+
| -9999999.99 |
+-------------+
1 row in set (0.00 sec)
mysql> quit
Bye
I think the first update should get an error as I specified a maximum of 9
digits. Per your description of numeric() the value should be set to
9999999.99. The fact that there is room for a sign character should not
increase the number of digits allowed in the field.
--
Jim Dickenson
mailto:[EMAIL PROTECTED]
Computers for Marketing Corporation
http://www.cfmc.com/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
