Professional Web Applications Themes

How to assign NULL instead of 0 for numeric INSERTs? - MySQL

Hi, My PHP application does a mass INSERT of about 3 dozen fields, many of them numeric. After I run the INSERT, however, MySQL places a bunch of "0"s (zeroes) in every numeric field, even if a null value was passed. I don't want to convert these fields to VARCHARs... so can I: - force a NULL insert - change default NULL insert behavior (i.e. don't put a 0 in!) or - easily run a post-INSERT one-line SQL statement to change all "0" field values to null? (MySQL 4.1.7) Thanks, DIZZLE...

  1. #1

    Default How to assign NULL instead of 0 for numeric INSERTs?

    Hi,


    My PHP application does a mass INSERT of about 3 dozen fields, many of
    them numeric. After I run the INSERT, however, MySQL places a bunch
    of "0"s (zeroes) in every numeric field, even if a null value was
    passed.

    I don't want to convert these fields to VARCHARs... so can I:
    - force a NULL insert
    - change default NULL insert behavior (i.e. don't put a 0 in!)
    or
    - easily run a post-INSERT one-line SQL statement to change all "0"
    field values to null?

    (MySQL 4.1.7)


    Thanks,
    DIZZLE
    Dizzledorf Guest

  2. #2

    Default Re: How to assign NULL instead of 0 for numeric INSERTs?

    Dizzledorf wrote: 

    I just tried entering NULL into an INT field a DECIMAL field and a FLOAT
    field and the contents were always NULL.
    You will most likely find that the field is set up with a default value of 0
    and it may also be defined as not null.
    You can change these settings with an ALTER TABLE command


    Paul Guest

  3. #3

    Default Re: How to assign NULL instead of 0 for numeric INSERTs?

     
    >
    > I just tried entering NULL into an INT field a DECIMAL field and a FLOAT
    > field and the contents were always NULL.
    > You will most likely find that the field is set up with a default value of[/ref]
    0

    A "default" only applies if no value is supplied. NULL is considered valid.
     

    In MySQL, this might be the thing and it will change your data silently.
    Oh, the horror.
     


    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
    MS SQL Server
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

Similar Threads

  1. weird results: (null!=null)??
    By alex in forum MySQL
    Replies: 7
    Last Post: September 28th, 07:33 AM
  2. 1-to-Many Inserts
    By Fandomlife in forum Macromedia ColdFusion
    Replies: 3
    Last Post: March 29th, 09:15 PM
  3. Replies: 1
    Last Post: December 6th, 01:55 AM
  4. Error: ?null? is null or not an object
    By Murray *TMM* in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 10th, 06:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139