Database Reference
In-Depth Information
er may accept the data. It will generate a warning message if the data given for the
columns don't match the column types. For instance, suppose we had tried to add another
row to the same table — this one for the bird family,
Anatidae
, the family for the Wood
Duck, another bird we entered already in the
birds
table. Suppose further that we had
tried to give the data in a different order from the way the columns are organized in the
table. The server would accept the SQL statement and process the data as best it can, but it
would not work the way we might want. The following example shows such a scenario:
INSERT INTO bird_families
VALUES('Anatidae', "This family includes ducks, geese and swans.",
NULL, 103);
Query OK, 1 row affected, 1 warning (0.05 sec)
Notice that in this SQL statement we put the family's name first, then the description, then
NULL for the
family_id
, and 103 for the
order_id
. MySQL is expecting the first
column to be a number or
DEFAULT
or NULL. Instead, we gave it text. Notice that the
status line returned by
mysql
after the
INSERT
statement says,
Query OK, 1 row affected,
1 warning
. That means that one row was added, but a warning message was generated, al-
though it wasn't displayed. We'll usethe
SHOW WARNINGS
statement like so to see the
warning message:
SHOW WARNINGS \G
*************************** 1. row ***************************
Level: Warning
Code: 1366
Message: Incorrect integer value: 'Anatidae' for column 'family_id'
at row 1
1 row in set (0.15 sec)
Here we can see the warning message: the server was expecting an integer value, but re-
ceived text for the column,
family_id
. Let's run the
SELECT
statement to see what we
have now in the
bird_families
table:
SELECT * FROM bird_families \G
*************************** 1. row ***************************
family_id: 100
scientific_name: Gaviidae
brief_description: Loons or divers are aquatic birds
found mainly in the Northern Hemisphere.
order_id: 103
*************************** 2. row ***************************
family_id: 101
