In mysql, I can set up a text field as "not null" but I cannot set default value for the text field.

Say then later on, if the insert query trying to insert a null value into that "not null" texgt field, what would happen? Would it automatically coverted to empty string?

Thanks!

    blackhorse;10934714 wrote:

    In mysql, I can set up a text field as "not null" but I cannot set default value for the text field.

    Say then later on, if the insert query trying to insert a null value into that "not null" texgt field, what would happen? Would it automatically coverted to empty string?

    Thanks!

    return this:
    ERROR 1048 (23000): Column 'content' cannot be null

      I run the query, try to insert null value to a text field set up as "not null", it will insert empty string. not error 1048.

      I am wondering that why we have different results.

      I am running php script to select from one table (with some null values) to insert into another table (with text fields set up as not null).

        Depending on which version of MySQL you're using and its configuration, it might be following its silly "oh I'll just silently do any old thing" principle that goes against the whole idea of database integrity.

        No other DBMS would be so whacked out as to insert an empty string if NULL was specified, and if the column was NOT NULL and the attempt was made the result would be as 3.grosz describes (23000 being the response mandated in the SQL standard).

          I'm guessing the differnce is due to different db engines, say myisam vs innodb. I'd only ever use myisam if I'd need full text indexing, and even then you could use innodb together with sphinx.

            I use mysql ver 5.1.37

            This code show me error (23000)

            CREATE TABLE t1 (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(12) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
INSERT INTO t1 (name) values (null);
ERROR 1048 (23000): Column 'name' cannot be null

              So you do get the expected result after all. Now I really don't understand what the problem is supposed to be.

                Weedpacket;10935052 wrote:

                Depending on which version of MySQL you're using and its configuration, it might be following its silly "oh I'll just silently do any old thing" principle that goes against the whole idea of database integrity.

                No other DBMS would be so whacked out as to insert an empty string if NULL was specified, and if the column was NOT NULL and the attempt was made the result would be as 3.grosz describes (23000 being the response mandated in the SQL standard).

                MySQL 5 is default to use strict_mode. So insert null value to not null field will generate error.

                But mysql4 is on normal_mode, and insert null value to the not null field will silently changed to empty string. But in my current project, I need it acts like this, due to I am reading data from 3rd party data export, some fields without value they just export either as null or empty string to me.

                Could I set up certain database on mysql 5 server to use the sql_mode as mysql4 but leave the server and other database sql_mode as the default strict_mode?

                Thanks!

                  To answer my own question, I couldn't set up sql_mode for individual database, but I can set it up for inidivdual session.

                  http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html

                  Here is the manual for mysql 4.1 about sql_mode.

                  My new question is that it seems that if I am on mysql 4.1, I have no way to set the mode to be strict_mode like mysql 5 default set up?

                  So insert / update will silently change the "invalid value" into "closest" value, like empty string or 0 for null? Even it is what I want in this project/database. But I don't want this sql_mode in other projects and other database running on my mysql 4.1 databse server.

                  Or there is a way that in mysql 4.1, that I can set sql_mode to strict_mode?

                  Thanks!

                    No idea. But why not change to 5.something?

                    From MySQL documentation page wrote:

                    End of Product Lifecycle. Active development and support for MySQL database server versions 3.23, 4.0, and 4.1 has ended. However, for MySQL 4.0 and 4.1, there is still extended support available. For details, see http://www.mysql.com/company/legal/lifecycle/#calendar. According to the MySQL Lifecycle Policy (see http://www.mysql.com/company/legal/lifecycle/#policy), only Security and Severity Level 1 issues will still be fixed for MySQL 4.0 and 4.1. Please consider upgrading to a recent version (MySQL 5.0 or 5.1).

                      Write a Reply...