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/...umn-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

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:
> 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/...umn-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 Surprise wrote:
> 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/...umn-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
>

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.