From: James Harvard
At 7:28 pm +0100 14/3/06, Martijn Tonies wrote:
Given that NULLs basically means the absence of a value (eg: unknown),
you shouldn't be storing NULLs.
At 1:49 pm -0500 14/3/06, Rhino wrote:
A null means unknown or not applicable and is a perfectly valid value
to use in
Martijn Tonies wrote:
We're arguing about whether or not your database design
(as stored on disk) should contain NULLs.
IMO: no, or at least as few as possible.
I believe the answer really is - *it depends*.
You are both right, really. Martijn, yes, according to academia and
proper
From: Mark Leith
Martijn Tonies wrote:
We're arguing about whether or not your database design
(as stored on disk) should contain NULLs.
IMO: no, or at least as few as possible.
I believe the answer really is - *it depends*.
You are both right, really. Martijn, yes, according to
But (in at least some situations) is not appropriate to record that you know
that you don't have a value?
I think the words of Donald Rumsfeld are appropriate here:
There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we
But (in at least some situations) is not appropriate to record that you
know that you don't have a value?
Sure it would. It it's needed.
But the answer doesn't have to be a NULL. Remember
the example about recording the fact that a student did
not make a test for whatever reason?
It was
Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:02:32:
Well, the question still is if you should store unknown at all ;)
Not according to Date: you should store what is known. See the remarks
about the true propositions, from which relational databases are
derived
(but you probably
Ah well, SQL (these days) provides plenty of functions to avoid
the problems. Coalesce being one of them...
How often do people write:
(probably not valid MySQL)
select firstname || coalesce(middlename || ' ', ' ') || lastname
from ...
Your syntax isn't quite correct; coalesce
From: [EMAIL PROTECTED]
Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:02:32:
Well, the question still is if you should store unknown at all ;)
Not according to Date: you should store what is known. See the remarks
about the true propositions, from which relational databases are
Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:32:45:
From: [EMAIL PROTECTED]
Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:02:32:
Well, the question still is if you should store unknown at all ;)
Not according to Date: you should store what is known. See the
in order to retrieve all the information about them. Replacing a
theoretically ugly null flag with a 10 way join strikes me, as an
engineer
rather than a theoretician, the wrong side of the
elegance/practicality
trade-off.
Using NULLs as well as de-normalization brings the risk
Also, surely if you left join to a table then if there's no matching row you
get a null value in your result set. So I'm not sure what the difference is
between getting that null because you store it in a column in the primary
table, and deriving it by left joining to a related table with no
James,
Also, surely if you left join to a table then if there's no matching row
you get a null value in your result set. So I'm not sure what the difference
is between getting that null because you store it in a column in the primary
table, and deriving it by left joining to a related table
Read the literature on how to design databases. What you do
is storing true propositions. That is, each attribute defines a
certain true proposition. For example:
Employee Martijn has Employee# 14.
You should be able to derive these sentences from every row.
Putting a NULL
RedRed!com IT Department wrote:
So, for my peace of mind, I would need to follow these procedures to
ensure Martijn's issue doesn't happen:
1. lock the table
2. execute my select
3. insert if it does not already exist
4. unlock the table
On a high level, is this a correct list of
And now we are down to reality. This is a MySQL list. Views are a wonderful
thing for creating an isolation layer between the application and the
database. However, MySQL's current implementation makes it extremely
difficult in many cases to avoid full table scans when you define the
logical view.
Hello Gordon,
And now we are down to reality. This is a MySQL list. Views are a
wonderful
thing for creating an isolation layer between the application and the
database. However, MySQL's current implementation makes it extremely
difficult in many cases to avoid full table scans when you
update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2
where column2='[EMAIL PROTECTED]'
And column1 = '[EMAIL PROTECTED]'
Or:
update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2
where b.column2=a.column1
And column1 = '[EMAIL PROTECTED]'
Regards!
-Mensaje original-
De: Truong Tan Son
AAAUUUGGGHHH!!! You used the dreaded comma separated list !!! ;-)
A more explicit way to write the same thing posted by Sr. Cruz...
update TABLE1 a
INNER JOIN TABLE2 b
on a.id = b.other_id
set a.FIELD1=b.FIELD2
WHERE a.column1='literal';
Actually posting a real query (instead of a
The fact that I use JOTM with Tomcat forces me to use a different data
source factory from the one described in the MySQL document Using
Connector/J with Tomcat,
(http://dev.mysql.com/doc/refman/4.1/en/cj-tomcat-config.html) -
org.objectweb.jndi.DataSourceFactory vs.
Hi,
I'm trying to see what speed I will get from a simple query like:
select * from bigTable
and I noticed the server- client transfer is always 512 bytes.
(iostat tells me I read 6348.80*1024 bytes/sec in a given time
period, and 12697.60 reads/sec, which translates to exactly 512)
I set
I need some help with improving INSERT performance. I am using JDBC. I
have minimized my indices as much as I can, changed to use batching, and
setup a thread pool for each transacted batch. Note that I am only using
InnoDB.
Now I am wondering what can be tweaked in the server properties. Right
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
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
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
I'm running MySQL 5.0.18 on OS X 10.4.5, on an intel MacBook. My
table definition is:
CREATE TABLE `UNIT` (
`UNIT_ID` int(11) NOT NULL auto_increment,
`UNIT_CODE` varchar(50) default NULL,
`UNIT_DESC` varchar(50) default NULL,
`USER_ID` int(11) NOT NULL,
`USER_DT` datetime NOT NULL,
I got an error 1025 trying to rename an InnoDB table. When I go to
look in the database now, that table isn't there even though there is
a .ibd file in the mysql data directory with the target name. If I
try to create a table with the target name (as an InnoDB table), I
get error 1005. I
At 15:22 -0600 3/16/06, Sterling Anderson wrote:
I'm running MySQL 5.0.18 on OS X 10.4.5, on an intel MacBook. My
table definition is:
CREATE TABLE `UNIT` (
`UNIT_ID` int(11) NOT NULL auto_increment,
`UNIT_CODE` varchar(50) default NULL,
`UNIT_DESC` varchar(50) default NULL,
`USER_ID`
On Mar 16, 2006, at 3:49 PM, Paul DuBois wrote:
This fails however. 'g/dl' != 'g/dL' though. I don't get why a
string field is being treated this way. I understand the HFS+
filesystem is not case sensitive but that shouldn't be an issued
with varchar fields should it?
No, it's not a
At 16:01 -0600 3/16/06, Sterling Anderson wrote:
On Mar 16, 2006, at 3:49 PM, Paul DuBois wrote:
This fails however. 'g/dl' != 'g/dL' though. I don't get why a
string field is being treated this way. I understand the HFS+
filesystem is not case sensitive but that shouldn't be an issued
with
Our mysql master machine crashed (hardware problem) and rebooted. When
it came back up, it started a new master log file. The slaves, however,
were all stuck on the last log file. They still showed both replication
threads running, no errors, but they just did not advance. I had to
Dear Sir,
All solutions is very good !
update TABLE1 a
INNER JOIN TABLE2 b
on a.id = b.other_id
set a.FIELD1=b.FIELD2
WHERE a.column1='literal';
Or
update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2
where column2='[EMAIL PROTECTED]'
And column1 = '[EMAIL PROTECTED]'
Or
update TABLE1 a,
Dear Sir,
How to know MySQL enable for ROLLBACK ?
Thanks you and best regards,
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi,
How to know MySQL enable for ROLLBACK ?
Use InnoDB tables.
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!
Hi.
I have the following table
| bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp |
Bid_Status |
+-+-+--++-++-+
| 1 |3 | 3 |12 |
Gregory Machin wrote:
Hi.
I have the following table
| bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp |
Bid_Status |
+-+-+--++-++-+
| 1 |3 |
35 matches
Mail list logo