MySQL DEFAULT Values

A default value is a value that is assigned to a column if no explicit value is provided during insertion.

It is useful if the user does not know what value to provide. 

The default value can be specified during the column definition, or it can be added later using the ALTER TABLE statement.  

What is in our table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
|    9 | XXX    | XXX_1      | Some            |      4562 |   500 |
+------+--------+------------+-----------------+-----------+-------+
9 rows in set (0.00 sec)

mysql>

Now we will add a new column called ShipCorp to the Stores table and sets the default value to 'Speedy Gonzales'.

mysql> ALTER TABLE Stores ADD ShipCorp VARCHAR (50) DEFAULT 'Speedy Gonzales';
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

This means that if a new row is inserted into the Stores table without specifying a value for ShipCorp, the default value 'Speedy Gonzales' will be used instead. 

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+-----------------+
| s_id | city   | store_name | product         | available | price | ShipCorp        |
+------+--------+------------+-----------------+-----------+-------+-----------------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 | Speedy Gonzales |
|    2 | London | London_2   | Gold PSU        |        75 |   100 | Speedy Gonzales |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 | Speedy Gonzales |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 | Speedy Gonzales |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 | Speedy Gonzales |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 | Speedy Gonzales |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 | Speedy Gonzales |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 | Speedy Gonzales |
|    9 | XXX    | XXX_1      | Some            |      4562 |   500 | Speedy Gonzales |
+------+--------+------------+-----------------+-----------+-------+-----------------+
9 rows in set (0.00 sec)

mysql>

New row:

mysql> INSERT INTO Stores(s_id, city, store_name, product, available, price)
    -> VALUES (NULL, 'xxx', 'yyy', 'ppp', 5421, 500);
Query OK, 1 row affected (0.07 sec)

mysql>

Our MySQL code inserts a new row into the "Stores" table with the following values for each column:

• "s_id": NULL (since it is an auto-increment column, the database will generate a unique value for this column automatically)
• "city": 'xxx'
• "store_name": 'yyy'
• "product": 'ppp'
• "available": 5421
• "price": 500

The statement is successful and affects 1 row, as confirmed by the message "Query OK, 1 row affected". 

mysql> SELECT * FROM Stores WHERE s_id=10;
+------+------+------------+---------+-----------+-------+-----------------+
| s_id | city | store_name | product | available | price | ShipCorp        |
+------+------+------------+---------+-----------+-------+-----------------+
|   10 | xxx  | yyy        | ppp     |      5421 |   500 | Speedy Gonzales |
+------+------+------------+---------+-----------+-------+-----------------+
1 row in set (0.00 sec)

mysql>

Our select instruction code gets all columns from the "Stores" table where the value of the "s_id" column is equal to 10. 

The result is a table with one row that matches the condition, showing the values of all columns in that row. 

mysql> ALTER TABLE Stores ALTER ShipCorp SET DEFAULT 'Daffy Duck';
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

We can change the default value of the "ShipCorp" column in the "Stores" table to 'Daffy Duck'. 

This means that if a new row is inserted into the "Stores" table without specifying a value for the "ShipCorp" column, it will be automatically set to 'Daffy Duck'.

Note that this change does not affect existing rows in the table - it only affects future inserts.

If you want to update the "ShipCorp" value for existing rows that do not have a value specified, you can use the UPDATE statement with a WHERE clause that checks for NULL values in the "ShipCorp" column. 

mysql> DESCRIBE Stores;
+------------+-------------+------+-----+------------+----------------+
| Field      | Type        | Null | Key | Default    | Extra          |
+------------+-------------+------+-----+------------+----------------+
| s_id       | int         | NO   | PRI | NULL       | auto_increment |
| city       | varchar(30) | YES  |     | NULL       |                |
| store_name | varchar(30) | YES  |     | NULL       |                |
| product    | varchar(50) | YES  |     | NULL       |                |
| available  | int         | YES  |     | NULL       |                |
| price      | int         | YES  |     | NULL       |                |
| ShipCorp   | varchar(50) | YES  |     | Daffy Duck |                |
+------------+-------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)

mysql>

New row:

mysql> INSERT INTO Stores(s_id, city, store_name, product, available, price)
    -> VALUES (NULL, 'bla', 'bla', 'bla', 1234, 350);
Query OK, 1 row affected (0.09 sec)

mysql>

The above code is inserting a new row into the "Stores" table with the following values:

• s_id: NULL (which will auto-increment since s_id is an auto-increment column)
• city: 'bla'
• store_name: 'bla'
• product: 'bla'
• available: 1234
• price: 350

Since the "ShipCorp" column has a default value set as 'Daffy Duck', it will automatically be set to that value for this new row. 

Check row 11, it's there.

mysql> SELECt * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+-----------------+
| s_id | city   | store_name | product         | available | price | ShipCorp        |
+------+--------+------------+-----------------+-----------+-------+-----------------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 | Speedy Gonzales |
|    2 | London | London_2   | Gold PSU        |        75 |   100 | Speedy Gonzales |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 | Speedy Gonzales |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 | Speedy Gonzales |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 | Speedy Gonzales |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 | Speedy Gonzales |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 | Speedy Gonzales |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 | Speedy Gonzales |
|    9 | XXX    | XXX_1      | Some            |      4562 |   500 | Speedy Gonzales |
|   10 | xxx    | yyy        | ppp             |      5421 |   500 | Speedy Gonzales |
|   11 | bla    | bla        | bla             |      1234 |   350 | Daffy Duck      |
+------+--------+------------+-----------------+-----------+-------+-----------------+
11 rows in set (0.00 sec)

mysql>