Why does MySQL return a message like 'returned an empty result set' or 'n row(s) affected'?

Why does MySQL return # MySQL returned an empty result set (i.e. zero rows). and 3 ro开发者_Go百科w(s) affected.? Is there anything wrong in my SQL statements?

CREATE TABLE IF NOT EXISTS `test` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `order` mediumint(8) NOT NULL,
  `url` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
  `content` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

# MySQL returned an empty result set (i.e. zero rows).

INSERT INTO `test` (`id`, `order`, `url`, `title`, `content`) VALUES
(52338, 1, '', 'Home', 'content'),
(70104, 2, 'about', 'About', 'content'),
(27034, 3, 'portfolio', 'Portfolio', 'content');
# 3 row(s) affected.

---

The number of affected rows and the length of the result set are two different things.

Generally, INSERT, UPDATE and DELETE statements affect rows, while SELECT returns a result set which may be empty if no rows were matched according to the condition.

---

Insert queries don't return any rows. The affected rows is basically how many rows were inserted. If one of the value sets you included had failed for some reason, you'd see "2 rows affected" instead of 3.

The same applies for delete and update queries - you're not FETCHING information from the database, you're just adding or changing data that was already there.

Only in the case of a SELECT query would rows be returned, and then only if any rows matched the conditions (where/having/joins) you set.

---

I just cut and pasted your code directly into a test database and it works fine.

lwdba@localhost (DB information_schema) :: create database test1;
Query OK, 1 row affected (0.02 sec)

lwdba@localhost (DB information_schema) :: use test1
Database changed

lwdba@localhost (DB test1) :: CREATE TABLE IF NOT EXISTS `test` 
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `order` mediumint(8) NOT NULL,
    `url` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
    `title` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
    `content` text COLLATE utf8_unicode_ci,
    PRIMARY KEY (`id`),
    UNIQUE KEY `url` (`url`) ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.08 sec)<BR>

The CREATE TABLE command is what echoed 0 rows affected if that's your concern.

lwdba@localhost (DB test1) :: INSERT INTO `test`
    (`id`, `order`, `url`,`title`, `content`) VALUES
    (52338, 1, '', 'Home', 'content'), 
    (70104, 2, 'about', 'About', 'content'),
    (27034, 3, 'portfolio', 'Portfolio', 'content');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

lwdba@localhost (DB test1) :: select * from test;
+-------+-------+-----------+-----------+---------+
| id    | order | url       | title     | content |
+-------+-------+-----------+-----------+---------+
| 52338 |     1 |           | Home      | content |
| 70104 |     2 | about     | About     | content |
| 27034 |     3 | portfolio | Portfolio | content |
+-------+-------+-----------+-----------+---------+
3 rows in set (0.00 sec)<BR>

---

mysql database is returned 0 rows

insert into studentfinace(Name,mother,class_id,level,fee,blance)

select studentfinace.Name,studentfinace.mother,class.Name,level.Name,level.fee, studentfinace.blance from studentfinace join class on class.ID=studentfinace.class_id join level on level.level_id=class.level_id where 1