other sql commands

sql

Question:  What do the special MySQL commands means?

Answer:

Hi,

Good question!

Those lines of code are for setting MySQL server system configuration variables. In tells MySQL what configurations to use when you are running the scripts.

This code was generated by MySQL when it performed a database dump. 

In most situations, this code is not required (the script will still work the same if you deleted those lines). However, it gives strict specific instructions to the MySQL database.

You can get full docs for all MySQL server system variables here
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

---

The general set up of these special commands

1. Prep Work: Keep a copy of original db setting in a temp variable called OLD_xyz

2. Current changes: Set db settings to a new value

3. Reset: At end of script, set the database back to original settings using data from OLD_xyz

===

There is a ton of stuff here ... so let's unpack it. :-)


Lines 9-18

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


The above lines run as prep work. It takes the current/db settings and saves them in variables called @OLD_xyz. Basically later in the script they are going to change some database. So this is the "Prep Work" that I mentioned in step 1 above.

For example, this line:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

It reads the current CHARACTER_SET_CLIENT  (that's the current character set of the client), and then assigns this to the  variable @OLD_CHARACTER_SET_CLIENT.

This is similar in Java world to saying:

String oldCharacterSetClient = characterSetClient;

The next couple of lines do a similar thing, this covers lines 9-11:


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

Docs: https://dev.mysql.com/doc/refman/5.7/en/set-variable.html

---

The next line of code (line 12) sets the session system variables to the given character set

/*!40101 SET NAMES utf8 */;

Docs: https://dev.mysql.com/doc/refman/5.7/en/set-names.html

---

The next couple of lines (lines 13-14) keeps a copy of the time zone and sets it to a new value

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;

Docs: https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

---

The next set of lines, keeps temp copies of settings for unique checks, foreigns keys etc. Basically we are doing variable assignments here

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Docs
https://dev.mysql.com/doc/refman/5.7/en/set-variable.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_unique_checks
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_foreign_key_checks

You can get full docs for all MySQL server system variables here
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

---

Now moving forward, let's look at code for creating a table:

--
-- Table structure for table `customer`
--

DROP TABLE IF EXISTS `customer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


The special MySQL commands

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;

copies the current character set into a temp variable called saved_cs_client.

It then sets the current character set to uft8.

Next it does the reqular SQL of creating the table

After it is complete then it resets the character set to the temp variabe saved_cs_client.

---

As you can see, it follows the approach that I mentioned:

1. Prep Work: Keep a copy of original db setting in a temp variable 
2. Current changes: Set db settings to a new value
3. Reset: At end of script, set the database back to original settings using data from temp variable


---

Let's move forward and look at an example of adding data.

Here's the code:

LOCK TABLES `customer` WRITE;
/*!40000 ALTER TABLE `customer` DISABLE KEYS */;

INSERT INTO `customer` VALUES 
    (1,'David','Adams','david@luv2code.com'),
    (2,'John','Doe','john@luv2code.com'),
    (3,'Ajay','Rao','ajay@luv2code.com'),
    (4,'Mary','Public','mary@luv2code.com'),
    (5,'Maxwell','Dixon','max@luv2code.com');

/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;


The code below disables the keys on the table. DISABLE KEYS tells MySQL to stop updating nonunique indexes.

/*!40000 ALTER TABLE `customer` DISABLE KEYS */;


At the end of the code segment we see enable keys. ENABLE KEYS tells MySQL to re-create missing indexes

/*!40000 ALTER TABLE `customer` ENABLE KEYS */;

---

The next line of code resets the time zone to original value

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

Docs: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_time_zone


---

The final set of lines just resets the MySQL server system variables back to original values.

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-09-24 21:50:59


=====


Well, that was good walk through.

Let me know if that clears it up for you.

:-)

Great Post!