Hi guys,
I am trying to work out how MYSQL deals with NULL values.
When a new column is inserted on a table already populated with records (40,000 in my case), and I specify it as NOT NULL, I can insert values without any issues. I was expecting some kind of DB errors to throw up.
This is when adding new records from both PhpMyAdmin & the PHP applications.
Does MYSQL have any utility to handle these cases? Like when a column is specified as Varchar it puts in '' or when its of type INT it puts in 0.
Thanks.
1 3184 Atli 5,058
Expert 4TB
Hey.
The way MySQL handles this depends on the mode it is running in. You may want to read this page for details on how this all works: 11.1.4. Data Type Default Values
For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time: - If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.
- If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
Implicit defaults are defined as follows: - For numeric types, the default is 0, with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence.
- For date and time types other than TIMESTAMP, the default is the appropriate “zero” value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See Section 11.3, “Date and Time Types”.
- For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Marcus |
last post by:
Hello, quick question about MySQL storing NULL values...
Say I have a textbox called $_POST and a variable $var.
if(empty($_POST))
$var = NULL;
else
$var = $_POST;
Disregarding...
|
by: larry |
last post by:
Ok I am working on building my skills to convert my apps to LAMP (read
I'm a semi noob), and there was one part I was thinking of. If I
create two identical MySQL tables (we'll say, invoice and...
|
by: Python_it |
last post by:
Python 2.4
MySQL-python.exe-1.2.0.win32-py2.4.zip
How can I insert a NULL value in a table (MySQL-database).
I can't set a var to NULL? Or is there a other possibility?
My var must be variable...
|
by: Dean Slindee |
last post by:
I am looking for the "right" way to handle inserting and presenting null
date values.
Public Const c_NullDate As Date = #12:00:00 AM#
If I set the value of a date variable in an SQL Server insert...
|
by: jwogick |
last post by:
I'm hoping someone can help me!
I just have a simple form with a datagrid displaying child records
from a dataset that contains two related tables Parent
table>Clients Child table>cases. using the...
|
by: DaFrizzler |
last post by:
Hi, I have received the following email from a colleague, and am quite
frankly baffled by the idea.
I am just wondering if anyone has any advice or suggestions about
this????
=== BEGIN MAIL...
|
by: pplers |
last post by:
As the tittle says, i would like to know if MySQL supports foreign keys in MyISAM tables. If so, how can it be done ???
|
by: shreedhan |
last post by:
Hi
I am just learning mysql and php
I use Fedora 6 for that
One thing I would like to ask is where does mysql store all those information?
like tables, databases, all?
I would like to save them...
|
by: aktar |
last post by:
After rummaging through mysqls website I've decided to ask the community.
Is it possible to show columns from multiple tables, eg:
SHOW COLUMNS FROM table_A, table_B
Furthermore, is it...
|
by: lazybee26 |
last post by:
Hello –
I’m trying to findout a better solution to this approach.
Currently if I have to return columns from multiple tables, I have to define my own TYPE and then return SETOF that type in...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |