Hello George!
On Sat, 24 Nov 2001, georgem wrote:
> Hi all,
>
> mysql v 3.23.32. I've started using mysqldump to backup my databases but
> have run into a strange problem with decimal numbers. As I understand it,
> mysql treats decimals like strings so to insert a value into a decimal field
> you would have to put quotes around it. However, mysqldump does not put
> quotes around the decimal values so you can't restore the correct info from
> the mysqldump.
'Can't restore them'? Meaning you tried it and it didn't work? This seems
to work:
# t1 script
use test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( d1 DECIMAL(4,4) );
INSERT INTO t1 VALUES (1.2), (-3.4), (5);
I ran that as 'mysql -p < t1'.
Then 'mysqldump -p test t1 > t1.sql' returns:
vvvvvvvvvvvvvvvvvvvvv
-- MySQL dump 8.17
--
-- Host: localhost Database: test
---------------------------------------------------------
-- Server version 4.0.0-alpha-debug-log
--
-- Table structure for table 't1'
--
CREATE TABLE t1 (
d1 decimal(6,4) default NULL
) TYPE=MyISAM;
--
-- Dumping data for table 't1'
--
INSERT INTO t1 VALUES (1.2000);
INSERT INTO t1 VALUES (-3.4000);
INSERT INTO t1 VALUES (5.0000);
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I then edited t1.sql and added 'use test; drop table if exists t1' at the
top and used 'mysql -p < t1.sql' to again import the data. 'Select * from
t1' Works fine.
Just for fun I changed the above t1 script to
INSERT INTO t1 VALUES ("1.2"), ('-3.4'), (5);
The script ran fine and dumped exactly the same values but no quotes.
I think they describe DECIMAL that way simply to make clearer the range of
values available by a given DECIMAL(x,y) declaration. 'Storage and
treatment as a string' doesn't necessarily mean that MySQL doesn't -know-
it's not 'a number.' LOL! If fact from a strictly LCD/binary/machine
language level of thought '-3.4' cannot even -be- 'a number' because it
includes the non-number '-' and '.' characters. Perfectly OK with me if
the db can deal with things automagically and -I- don't have to muck
around with all those lower level conversion details. :)
Have a :) day!
jb
--
jim barchuk
[EMAIL PROTECTED]
---------------------------------------------------------------------
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