NULL columns
When doing an insert using NULL in the insert request, what really is being written in the column? Is the word NULL being written? Is any real space being consumed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NULL columns
When doing an insert using NULL in the insert request, what really is being written in the column? Is the word NULL being written? Is any real space being consumed? In the cases that you really want to store NULL ;-) No, it's not the word null. Of course space is consumed. How much depends, see, for example: http://dev.mysql.com/doc/refman/5.0/en/static-format.html or http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html This is for MyISAM, InnoDB behaves differently. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NULL columns
Hi! - Original Message - From: Martijn Tonies [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 16, 2006 8:59 PM Subject: Re: NULL columns When doing an insert using NULL in the insert request, what really is being written in the column? Is the word NULL being written? Is any real space being consumed? In the cases that you really want to store NULL ;-) No, it's not the word null. Of course space is consumed. How much depends, see, for example: http://dev.mysql.com/doc/refman/5.0/en/static-format.html or http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html This is for MyISAM, InnoDB behaves differently. InnoDB's old table format in 4.1 and earlier kept a fixed-length column the same length even when the value NULL was stored. That, of course, wasted a lot of space. The advantage was less fragmentation in updates. InnoDB's new table format in 5.0 does not use any space to store a NULL. The column itself needs one bit to indicate whether the value is NULL or not. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GIS - NULL columns
I installed mySQL server from Wizard and then i create table:
create table geom ( g POINT) ENGINE = MYISAM;
but i can't add any object to the table. I wrote:
insert into geom values(PointFromText('POINT(1,1)'));
and then i saw in the table NULL values:
SELECT AsText(g) FROM geom;
| g|
| NULL |
| NULL |
What can i do?? Do i set some variables??
My ini files:
#This File was made using the WinMySQLAdmin 1.4 Tool
#2004-11-08 23:39:15
#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions
[mysqld]
basedir=D:/MySQL/MySQL Server 4.1
#bind-address=10.1.10.34
datadir=D:/MySQL/MySQL Server 4.1/data
#language=D:/MySQL/MySQL Server 4.1/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
[WinMySQLadmin]
Server=D:/MySQL/MySQL Server 4.1/bin/mysqld-nt.exe
user=
password=
Re: GIS - NULL columns
In article [EMAIL PROTECTED],
Rafal K. [EMAIL PROTECTED] writes:
I installed mySQL server from Wizard and then i create table:
create table geom ( g POINT) ENGINE = MYISAM;
but i can't add any object to the table. I wrote:
insert into geom values(PointFromText('POINT(1,1)'));
and then i saw in the table NULL values:
SELECT AsText(g) FROM geom;
| g|
| NULL |
| NULL |
You need 'POINT(1 1)' instead of 'POINT(1,1)'. It's a pity that MySQL
silently does something else instead of complaining loudly if the
input is incorrect.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GIS - NULL columns
Hello.
Remove coma from 'POINT(1,1)', instead use 'POINT(1 1)';
See:
http://dev.mysql.com/doc/mysql/en/Populating_spatial_columns.html
I installed mySQL server from Wizard and then i create table:
create table geom ( g POINT) ENGINE = MYISAM;
but i can't add any object to the table. I wrote:
insert into geom values(PointFromText('POINT(1,1)'));
and then i saw in the table NULL values:
SELECT AsText(g) FROM geom;
| g|
| NULL |
| NULL |
What can i do?? Do i set some variables??
My ini files:
#This File was made using the WinMySQLAdmin 1.4 Tool
#2004-11-08 23:39:15
#Uncomment or Add only the kRafal K. [EMAIL PROTECTED] wrote:
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: inserting null to not null columns
Hi Donna, You have to include the column `type` in the SQL-statement, otherwise the not specified column will have its indirect null translated to an empty string or 0 (for strings/numeric). A bug or a feature? Actually I've used it as a feature sometimes when using MySQL to move large amount of dirty data between different systems. Be careful when not including NOT NULL columns in your inserts... To get around the problem and get more background info, read: http://dev.mysql.com/doc/mysql/en/constraint_NOT_NULL.html Regards, Thomas Lundström, Ongame E-Solutions AB -Original Message- From: Donna Hinshaw [mailto:[EMAIL PROTECTED] Sent: den 21 september 2004 21:07 To: [EMAIL PROTECTED] Subject: inserting null to not null columns Hi folks: I have an InnoDB database, the tables created using MySQL Control Center 0.9.4-beta (winXP pro platform). Each table has some columns which I have checked as Nulls Allowed. I am building a pure Java GUI to the database. Got the SQL statements working fine, but have discovered that I can successfully insert rows into a table without including a value for a column which should be blocking nulls. e.g. Table A id (PK, auto increment) name type ssn ( nulls allowed specified) == name and type do not have nulls allowed specified, so I think they should be NOT NULL. they also have no default specified. then insert into A (id,name,ssn) values (NULL,Jane,9) this statement works fine, but I think it should give me an error by saying that I'm trying to insert a row without providing a value for the type column (which has no default specified). Looking at the create statement for the tables, MySQL Control Center has supplied defaults of blanks...can I turn off that preference ? using MySQL 4.0.18 Can anyone provide clarification? thanks... Donna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting null to not null columns
Hi folks: I have an InnoDB database, the tables created using MySQL Control Center 0.9.4-beta (winXP pro platform). Each table has some columns which I have checked as Nulls Allowed. I am building a pure Java GUI to the database. Got the SQL statements working fine, but have discovered that I can successfully insert rows into a table without including a value for a column which should be blocking nulls. e.g. Table A id (PK, auto increment) name type ssn ( nulls allowed specified) == name and type do not have nulls allowed specified, so I think they should be NOT NULL. they also have no default specified. then insert into A (id,name,ssn) values (NULL,Jane,9) this statement works fine, but I think it should give me an error by saying that I'm trying to insert a row without providing a value for the type column (which has no default specified). Looking at the create statement for the tables, MySQL Control Center has supplied defaults of blanks...can I turn off that preference ? using MySQL 4.0.18 Can anyone provide clarification? thanks... Donna
null columns after loading
I'm trying to insert records from a file. These records are separated with pipes. use crous; load data local infile aid.exp into table aid; When I do select * from aid , the first column is OK but the second and third columns are nulls for all rows. It's weird! Has anyone experienced this problem? I'm working on linux with mysql v. 3.22.32. NTMail K12 - the Mail Server for Education - 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
Re: null columns after loading
GUYOT Carole writes: I'm trying to insert records from a file. These records are separated with pipes. load data local infile aid.exp into table aid; How is MySQL supposed to know that you're using a non-standard way of separating your records if you don't tell it? :-O You need to specify SEPARATED BY and possibly some more stuff. Look at the chapter on LOAD DATA INFILE in the manual and all will become clear. Hopefully. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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
Average of all NON-NULL columns in a ROW?
Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? Thanks! Graeme - 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
Re: Average of all NON-NULL columns in a ROW?
On Tue, May 22, 2001 at 12:29:35PM -0400, Graeme B. Davis wrote: Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? SELECT SUM(field) / COUNT(field) FROM table WHERE othercondition AND (field IS NOT NULL) G'luck, Peter -- I am the meaning of this sentence. - 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
Re: Average of all NON-NULL columns in a ROW?
Select avg(id1) from tableName where id1 is not null; ? - Original Message - From: Graeme B. Davis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 11:29 AM Subject: Average of all NON-NULL columns in a ROW? Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? Thanks! Graeme - 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 - 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
Re: Average of all NON-NULL columns in a ROW?
Ahh.. that, too :) G'luck, Peter -- If I were you, who would be reading this sentence? On Tue, May 22, 2001 at 12:04:34PM -0500, Cal Evans wrote: Select avg(id1) from tableName where id1 is not null; ? - Original Message - From: Graeme B. Davis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 11:29 AM Subject: Average of all NON-NULL columns in a ROW? Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? - 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
Re: Average of all NON-NULL columns in a ROW?
No, I'm looking for the average of all not NULL columns inside a certain _record/row_. There are other columns in the each row. Right now I have to do this: (these are survey questions, which don't necessarily need to be answered) SELECT IF(q1a,@cnt:=1,@cnt:=0), IF(q1b,@cnt:=@cnt+1,0), IF(q1c,@cnt:=@cnt+1,0), IF(q1d,@cnt:=@cnt+1,0), IF(q1e,@cnt:=@cnt+1,0), IF(q1f,@cnt:=@cnt+1,0), IF(q1g,@cnt:=@cnt+1,0), IF(q1h,@cnt:=@cnt+1,0), IF(q1i,@cnt:=@cnt+1,0), IF(q1j,@cnt:=@cnt+1,0), IF(q7,@cnt:=@cnt+1,0), IF(q8,@cnt:=@cnt+1,0), IF(q9,@cnt:=@cnt+1,0), ROUND((q1a+q1b+q1c+q1d+q1e+q1f+q1g+q1h+q1i+q1j+q7+q8+q9)/@cnt,2) AS surveyavg, This works but looks really bad and I was wondering if there is another way to do this. If this data were in columns, I could just AVG(q1a) and GROUP BY q1a, but this data is in each row. Ideas? graeme - Original Message - From: Cal Evans [EMAIL PROTECTED] To: Graeme B. Davis [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 1:04 PM Subject: Re: Average of all NON-NULL columns in a ROW? Select avg(id1) from tableName where id1 is not null; ? - Original Message - From: Graeme B. Davis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 11:29 AM Subject: Average of all NON-NULL columns in a ROW? Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? Thanks! Graeme - 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 - 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 - 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
UNIQUE with NULL columns
with MySQL 3.23.33 on Linux, shouldn't UNIQUE columns that contain NULL values still reject rows that are the same? this isn't working (it allows duplicate rows to be included if one of the columns is NULL). this seems wrong. here's an example: mysql create table n ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a VARCHAR(10) NOT NULL, b VARCHAR(10) NULL, c VARCHAR(10) NULL, UNIQUE(a, b, c) ); Query OK, 0 rows affected (0.00 sec) mysql describe n; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | a | varchar(10) | | MUL | || | b | varchar(10) | YES | | NULL|| | c | varchar(10) | YES | | NULL|| +---+-+--+-+-++ 4 rows in set (0.00 sec) mysql insert into n values(NULL, 'a', 'b', 'c'); Query OK, 1 row affected (0.00 sec) mysql insert into n values(NULL, 'a', 'b', 'c'); ERROR 1062: Duplicate entry 'a-b-c' for key 2 perfect mysql insert into n values(NULL, 'a', 'b', NULL); Query OK, 1 row affected (0.00 sec) mysql insert into n values(NULL, 'a', 'b', NULL); Query OK, 1 row affected (0.00 sec) why is this allowed? mysql select * from n; ++---+--+--+ | id | a | b| c| ++---+--+--+ | 1 | a | b| c| | 2 | a | b| NULL | | 3 | a | b| NULL | ++---+--+--+ 3 rows in set (0.00 sec) mysql insert into n values(NULL, 'a', NULL, 'c'); Query OK, 1 row affected (0.00 sec) mysql insert into n values(NULL, 'a', NULL, 'c'); Query OK, 1 row affected (0.00 sec) why is this allowed? mysql select * from n; ++---+--+--+ | id | a | b| c| ++---+--+--+ | 1 | a | b| c| | 2 | a | b| NULL | | 3 | a | b| NULL | | 4 | a | NULL | c| | 5 | a | NULL | c| ++---+--+--+ 5 rows in set (0.00 sec) did i make a mistake or misunderstand something? or is this a bug? john - 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
Re: UNIQUE with NULL columns
Hello john, Thursday, March 08, 2001, 12:19:22 AM, you wrote: jrd with MySQL 3.23.33 on Linux, shouldn't UNIQUE columns that contain NULL jrd values still reject rows that are the same? this isn't working (it allows jrd duplicate rows to be included if one of the columns is NULL). this seems wrong. No, this is absolutely correct and any database system should act like this. NULL means no that conetn is not specified, it is not '0' or empty string. If you compare NULL value to NULL value then (NULL=NULL) is not TRUE, but also NULL. If you specified column as NULL that only means it can't contain same values, but NULL is no value so you can have many records with NULL entries in column. And you can always set NOT NULL too... -- Best regards, Rimantasmailto:[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
