Asked  7 Months ago    Answers:  5   Viewed   37 times

I have created a table with NOT NULL constraints on some columns in MySQL. Then in PHP I wrote a script to insert data, with an insert query. When I omit one of the NOT NULL columns in this insert statement I would expect an error message from MySQL, and I would expect my script to fail. Instead, MySQL inserts empty strings in the NOT NULL fields. In other omitted fields the data is NULL, which is fine. Could someone tell me what I did wrong here?

I'm using this table:

CREATE TABLE IF NOT EXISTS tblCustomers (
  cust_id int(11) NOT NULL AUTO_INCREMENT,
  custname varchar(50) NOT NULL,
  company varchar(50),
  phone varchar(50),
  email varchar(50) NOT NULL,
  country varchar(50) NOT NULL,
  ...
  date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (cust_id)
) ;

And this insert statement:

$sql = "INSERT INTO tblCustomers (custname,company) 
        VALUES ('".$customerName."','".$_POST["CustomerCompany"]."')";
$res = mysqli_query($mysqli, $sql);

Or using bind variables:

$stmt = mysqli_prepare($mysqli, "INSERT INTO tblCustomers (custname,company, email, country) VALUES (?, ?, ?, ?)");

mysqli_stmt_bind_param($stmt, 'ssss', $customerName, $_POST["CustomerCompany"], $_POST["CustomerEmail"], $_POST["AddressCountry"]);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

 Answers

12

If you're sure you're not using explicit default values, then check your strict mode:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

MySQL Data Type Default Values

As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, 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 not enabled, MySQL sets the column to the implicit default value for the column data type.

  • If strict 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.

Server SQL Modes

Wednesday, March 31, 2021
 
nika
answered 7 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
68

@Igor is quite right and a couple of OR'ed expression are fast and simple.

For a long list of columns (a, b, c, d, e, f, g in the example), this is shorter and just as fast:

CHECK (NOT (a,b,c,d,e,f,g) IS NULL)

db<>fiddle here
Old SQL Fiddle.

How does it work?

A more verbose form of the above would be:

CHECK (NOT ROW(a,b,c,d,e,f,g) IS NULL)

ROW is redundant syntax here.

Testing a ROW expression with IS NULL only reports TRUE if every single column is NULL - which happens to be exactly what we want to exclude.

It's not possible to simply reverse this expression with (a,b,c,d,e,f,g) IS NOT NULL, because that would test that every single column IS NOT NULL. Instead, negate the whole expression with NOT. Voilá.

More details in the manual here and here.

An expression of the form:

CHECK (COALESCE(a,b,c,d,e,f,g) IS NOT NULL)

would achieve the same, less elegantly and with a major restriction: only works for columns of matching data type, while the check on a ROW expression works with any columns.

Wednesday, June 9, 2021
 
godot
answered 5 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 4 Months ago
46

In MySQL, each column type has an "implicit default" value.

For string types [the implicit] default value is the empty string.

If a NOT NULL column is added to a table, and no explicit DEFAULT is specified, the implicit default value is used to populate the new column data1. Similar rules apply when the DEFAULT value is specified.

As such, the original DDL produces the same results as:

-- After this, data will be the same, but schema has an EXPLICIT DEFAULT
ALTER TABLE t ADD c varchar(10) NOT NULL DEFAULT ''
-- Now we're back to the IMPLICIT DEFAULT (MySQL stores NULL internally)
ALTER TABLE t ALTER c DROP DEFAULT

The "strict" mode settings affects DML statements relying on default values, but do not affect the implicit default usage when the column is added.

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 [and if] strict SQL mode is enabled, an error occurs ..

Here is an sqlfiddle "proof" that strict mode does not apply to the ALTER TABLE .. ADD statement.


1 This is a MySQL feature. Other engines, like SQL Server, require an explicit DEFAULT (or NULL column) constraint for such schema changes.

Monday, August 9, 2021
 
Yigang Wu
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share