Professional Web Applications Themes

NULLs and column re-ordering - MySQL

Greetings, Though I am an old grey-headed programmer I am nearly clueless about SQL of any flavor. Fortunately there is the browser. I see a lot of error messages in our start up log complaining that the default field is " ' ' " (spaces added for readability) instead of ''. The problem fields have nothing in them as opposed to the little grey NULL box. No one here can tell me how to generate this null. I have tried various permutations of

Thread: NULLs and column re-ordering

and single quotes to no avail. Secondly, some of the table columns are out of order. ...

  1. #1

    Default NULLs and column re-ordering


    Greetings,


    Though I am an old grey-headed programmer I am nearly clueless about
    SQL of any flavor. Fortunately there is the browser. I see a lot of
    error messages in our start up log complaining that the default field
    is " ' ' " (spaces added for readability) instead of ''. The problem
    fields have nothing in them as opposed to the little grey NULL box. No
    one here can tell me how to generate this null. I have tried various
    permutations of \0 and single quotes to no avail.

    Secondly, some of the table columns are out of order. I don't think it
    should matter since the names are unique but change the order are my
    instructioins. I have been able to re-order by deleting and then
    inserting-after. The trouble is that I lose the data that was in the
    original column. Is there a simple method for re-arranging columns and
    keeping data intact?

    I looked at the article at http://dev.mysql.com/doc/refman/5.0/en/change-column-order.html
    but I get a syntax error on 'INSERT INTO p2 Select columns in-new-
    order FROM personnel' that is near 'columns in-new-order'. Even if
    this method works I have a large number of columns. Is there a way to
    import the names and their attributes or must all of that be hand
    written?

    Thanks,

    jh

    HMS Guest

  2. #2

    Default Re: NULLs and column re-ordering

    Hi,

    you can set if a column is allowed to be value=NULL which is not the
    same as value='' for a text/varchar or value=0 for an integer. It does
    not mean empty or zero, it means that the value is not set. This can be
    useful but it can also make problems e.g. using string-functions or
    calculating with NULL usually has strange results.

    Perhaps an idea for reordering your columns is to do a mysqldump, edit
    it in a text editor and import the dump back again. I am not sure which
    other ways are possible.


    HMS Surprise schrieb: 
    Tobias Guest

  3. #3

    Default Re: NULLs and column re-ordering

    HMS Surprise wrote: 

    for any field that you'd like to set to NULL, you can run this cmd:

    update table1 set field1=NULL where field2=999;

    order is of no relevance in a relational database because you can
    rearrange the order of the data in several ways: you can run select
    statements and list the fields that you want in your specific order. for
    example if your table looks something like this:
    id
    name
    desc
    address
    city

    in that order, you can run a select statement that retrieves the data in
    this way:
    select name, address, desc, city from table

    further you can sort the data retrieved by adding an 'order by' clause
    to any select statement such as this:
    select name, address, desc, city from table order by city,name

    hope this helps.
    lark Guest

Similar Threads

  1. NULLS first ...
    By Ralph in forum MySQL
    Replies: 4
    Last Post: November 7th, 02:00 AM
  2. dealing with nulls....
    By Ken Schaefer in forum ASP
    Replies: 3
    Last Post: July 7th, 05:49 AM
  3. Problem with nulls
    By Bob in forum ASP.NET General
    Replies: 1
    Last Post: July 2nd, 08:38 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