in reply to Typeless bind fails for certain large numbers with MySQL decimal

Unless you plan on doing various numerical operations (like addition), I would store them as CHAR(19) (or whatever your maximum is). Just because the input is limited to 0-9 doesn't mean you have to store it as a numerical field. Not to mention, actually, that storing it as a CHAR() will still allow for proper sorting (if that is important).

As for why this is happening, I'm also going to guess that there is a lossy conversion somewhere. The MySQL documentation says that calculations should be accurate up to 65 digits, but it also depends on what Perl, DBD::mysql, and everyone else in the chain is doing.

My criteria for good software:
  1. Does it work?
  2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
  • Comment on Re: Typeless bind fails for certain large numbers with MySQL decimal

Replies are listed 'Best First'.
Re^2: Typeless bind fails for certain large numbers with MySQL decimal
by herveus (Prior) on May 28, 2008 at 14:21 UTC
    Howdy!

    I'd put it even more strongly. If the data is a string of digits with no numeric significance, then it isn't numeric data, but character data. Further, if there are values with leading zeros (such as ZIP codes), you have to store it as character data or else jump through extra hoops to reapply the leading zeros. Character data won't give you that problem.

    yours,
    Michael
[reply]
Re^2: Typeless bind fails for certain large numbers with MySQL decimal
by andreas1234567 (Vicar) on May 28, 2008 at 16:55 UTC
    I disagree. Most databases (and finally in MySQL 5.0.2 and up) have built in constraint checks that are very valuable. I'm not a US citizen but I strongly suspect a ZIP code containing letters makes little sense. I feel the database should not allow values not adhering to the column specification. 
    mysql> drop table if exists foo;
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo (i numeric(23,0) primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   | 
+-------------------+
1 row in set (0.00 sec)

mysql> insert into foo values ('a');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values ('d');
ERROR 1366 (HY000): Incorrect decimal value: 'd' for column 'i' at row
+ 1
mysql>

    [download]
    See also: 1.8.6.2. Constraints on Invalid Data
    --
    No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]
[reply]
[d/l]
      Type constraints are different from check constraints. All databases provide type constraints, MySQL allowing you to turn them on or off as desired. Check constraints, however, are more comprehensive.

      In this case, let's say you have a DECIMAL(23) for this 19 digit account number. What's preventing a 12 digit entry? In other words, the application will need to do a good amount of validation anyways. Anything the database provides in terms of type constraints is bonus. Check constraints, imho, are better implemented as a service wrapping the database connection or as a set of libraries. I personally don't like embedding constraints in the database because I feel that puts too much work into the datastore.

      My criteria for good software:
      1. Does it work?
      2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]

In Section Seekers of Perl Wisdom