
Photo by Carl Nenzen Loven on Unsplash
This third installment of the MySQL Beginner Series Basics will highlight INSERT
syntax for populating tables with data. Be sure and visit both blog posts: CREATE Database, User, and Table – Easy examples with MySQL along with, ALTER TABLE: Examples with MySQL – Beginner Series, for an overview of the material covered so far in the series.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.
Objectives.
- Perform a single
INSERT
using mysql interactively from the command line. - Perform multiple
INSERTS
using mysql interactively from the command line. - Use mysql in batch mode to
INSERT
multiple rows via a.sql
source file.
Insert Syntax
INSERT
populates an existing table with new rows of data.
Basic INSERT
syntax can be as follows:
VALUES (list of values to insert) or
[multiple lists of values
(list of values to insert),
(list of values to insert),
(list of values to insert),]
etc...;
Single Inserts
To INSERT
a single record interactively from the command line, the following syntax can be used:
-> VALUES('John','Tom','318-767-8818',32);
Query OK, 1 row affected (0.08 sec)
This INSERT
statement lists out the column names in a comma-separated, parenthesized list containing the columns to populate. Followed by the VALUES
keyword and another comma-separated list of the actual values to be inserted into the listed columns.
Notice for this INSERT
, the f_bday
column was not specified, nor any value for it either.
Yet when I query the friends
table for that row of data,
-> WHERE f_last_name = 'Tom';
+-----------+--------------+-------------+--------------+-------+--------+
| friend_id | f_first_name | f_last_name | f_cell_phone | f_age | f_bday |
+-----------+--------------+-------------+--------------+-------+--------+
| 1 | John | Tom | 318-767-8818 | 32 | NULL |
+-----------+--------------+-------------+--------------+-------+--------+
1 row in set (0.04 sec)
NULL
is present for the f_bday
column.
So what gives?
Since f_bday
was not specified in the column list or list of values, MySQL automatically populated it with a DEFAULT
value, NULL
, which was specified during column creation.
More to come on excluding column names and or values as we progress through this blog post.
Multiple Inserts
Multiple rows can be inserted interactively from the command line using similar syntax as follows:
-> VALUES
-> ('John','Roberts','555-867-5309', 36, '1981-04-17'),
-> ('Billy','Bogus','777-383-2828', 44, '1973-08-03'),
-> ('Jennifer','Kelly','979-191-6652',39, '1978-10-16');
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
Notice after the VALUES
syntax, multiple parenthesized comma-separated list, each containing column values, are themselves separated by commas.
Information String:
Records: number Duplicates: number Warning: number
The above INSERT
returns an information string, which I directly relay its interpretation from the official documentation (see link in Closing section):
“Records indicates the number of rows processed by the statement. (This is not necessarily the number of rows actually inserted because Duplicates can be nonzero.) Duplicates indicate the number of rows that could not be inserted because they would duplicate some existing unique index value. Warnings indicate the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions.”
(See the official documentation for the Warning conditions.)
Default values.
Let’s try a single INSERT
without a parenthesized list of column names:
-> VALUES ('Mark', 'Harmon', '567-733-0833', 28, '1989-06-27');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
Uh oh. That didn’t work.
By not providing a list of column names, values for every column must be present in the VALUES
syntax list.
To accomplish this, I use the DEFAULT
keyword for the friend_id
column value. Since this column has the AUTO_INCREMENT
attribute, it will automatically INSERT
the next available sequential value.
Query OK, 1 row affected (0.02 sec)
Querying for the f_first_name
value of Mark
, returns a full row of data with a friend_id
column value of 5
.
+-----------+--------------+-------------+--------------+-------+------------+
| friend_id | f_first_name | f_last_name | f_cell_phone | f_age | f_bday |
+-----------+--------------+-------------+--------------+-------+------------+
| 5 | Mark | Harmon | 567-733-0833 | 28 | 1989-06-27 |
+-----------+--------------+-------------+--------------+-------+------------+
1 row in set (0.00 sec)
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Batch Mode For Multiple Inserts
Finally, I will cover my personal favorite method for bulk inserts. Using mysql in batch mode.
I have a batch_insert.sql
file containing these inserts:
VALUES
('Jeremy', 'Hall', '777-272-1717',40 , '1977-02-07'),
('Josh', 'Remey', '727-213-7119',40 , '1977-03-17'),
('Jenny', 'Marcum', '543-179-1548',37 , '1980-09-27'),
('Mike', 'Comb', '118-972-1919',30 , '1987-10-05'),
('Thurmon', 'Docker', '393-392-1010',25 , '1992-08-19'),
('Milton', 'Hammerfast', '318-272-0797',41 , '1976-05-11'),
('Nikki', 'Vernon', '548-498-9949',28 , '1989-01-31'),
('Avis', 'Pearl', '509-292-8787',64 , '1953-07-04');
To execute this .sql
file, simply prefix either a \.
or the source
word before the file name as demonstrated below.
Note: For demonstration purposes, the file path presented below is generic. My actual system file path for this script file is not shown.
Query OK, 8 rows affected (0.06 sec)
Records: 8 Duplicates: 0 Warnings: 0
For visual confirmation, I issue a simple SELECT
query for just the f_first_name
column (Note: There are additional names already present in the table so the result set will have more than 8 rows.),
+--------------+
| f_first_name |
+--------------+
| John |
| John |
| Billy |
| Jennifer |
| Mark |
| Jeremy |
| Josh |
| Jenny |
| Mike |
| Thurmon |
| Milton |
| Nikki |
| Avis |
| Josh |
+--------------+
14 rows in set (0.00 sec)
Using batch mode, in my opinion, is an effective way for large, bulk inserts.
An overview of the entire friends
table, containing all the inserts performed in this blog post (among others already present in the table),
mysql> SELECT * FROM friends;
+-----------+--------------+-------------+--------------+-------+------------+
| friend_id | f_first_name | f_last_name | f_cell_phone | f_age | f_bday |
+-----------+--------------+-------------+--------------+-------+------------+
| 1 | John | Tom | 318-767-8818 | 32 | NULL |
| 2 | John | Roberts | 555-867-5309 | 36 | 1981-04-17 |
| 3 | Billy | Bogus | 777-383-2828 | 44 | 1973-08-03 |
| 4 | Jennifer | Kelly | 979-191-6652 | 39 | 1978-10-16 |
| 5 | Mark | Harmon | 567-733-0833 | 28 | 1989-06-27 |
| 6 | Jeremy | Hall | 777-272-1717 | 40 | 1977-02-07 |
| 7 | Josh | Remey | 727-213-7119 | 40 | 1977-03-17 |
| 8 | Jenny | Marcum | 543-179-1548 | 37 | 1980-09-27 |
| 9 | Mike | Comb | 118-972-1919 | 30 | 1987-10-05 |
| 10 | Thurmon | Docker | 393-392-1010 | 25 | 1992-08-19 |
| 11 | Milton | Hammerfast | 318-272-0797 | 41 | 1976-05-11 |
| 12 | Nikki | Vernon | 548-498-9949 | 28 | 1989-01-31 |
| 13 | Avis | Pearl | 509-292-8787 | 64 | 1953-07-04 |
| 14 | Josh | Otwell | 318-282-2075 | 41 | NULL |
+-----------+--------------+-------------+--------------+-------+------------+
14 rows in set (0.00 sec)
Insert Takeaways For This Post…
-
-
- A user needs the
INSERT
privilege for the specified table to be populated. - Column names may be supplied in any of the following ways:
-
- A parenthesized list of comma-separated column names. In this instance, a value for each specified column must be in the
VALUES
list. (Also applies for inserting with theSELECT
statement, however that functionality is not covered in this blog post.) - If not specifying a list of column names, values must be supplied for every column in the table, in order. However the
DEFAULT
keyword can be used for those columns that have aDEFAULT
value set at table creation.
- A parenthesized list of comma-separated column names. In this instance, a value for each specified column must be in the
-
- Column values can supplied as follows:
- When strict SQL mode is not enabled, column values not explicitly provided, are set to the
DEFAULT
value for that column, with either the table-defined or data typeDEFAULT
. INSERT
produces an error if no values are specified for all columns that don’t have a default value when strict SQL mode is enabled.INSERT INTO table_name () VALUES ();
– Is allowable, yet MySQL handles this as follows:-
- When strict mode is not enabled, an implicit
DEFAULT
value is used for a column that has no explicitly definedDEFAULT
. - An error occurs if any columns have no default value when strict mode is enabled.
- The
DEFAULT
keyword can be used to explicitly set a column to its default value.
- When strict mode is not enabled, an implicit
-
- When strict SQL mode is not enabled, column values not explicitly provided, are set to the
- A user needs the
-
Closing.
Inserts are a powerful feature of MySQL and are a necessity for database operations. After all, the tables need data, right?
Stay tuned to this continuing series as the next blog post will look into two of the more powerful MySQL commands: UPDATE
and DELETE
.
Feel free to visit the official MySQL 5.7 Online Manual for further study or questions.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Have I mentioned how much I love a cup of coffee?!?!
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
2 thoughts on “Populating a MySQL table with the INSERT statement – Beginner Series.”