13 Most Asked Questions On MySQL
Tags: mysql
Published: 2020-08-14 10:53:07

MySQL is an open-source relational database management system. As the name suggests, it is used to create, update, and store databases. It is based on SQL (Structured Query Language). So, today we will be checking out the 13 most asked questions on MySQL.

## 13 Most Asked Questions On MySQL

### 1. How to prevent SQL injection in PHP?

**Answer:**

**Use prepared statements and parameterized queries.** These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL. You basically have two options to achieve this:

i. Using <a href="https://www.php.net/manual/en/book.pdo.php">PDO</a> (for any supported database driver):

```
$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute([ 'name' => $name ]);

foreach ($stmt as $row) {
    // Do something with $row
}
```

ii. Using <a href="https://www.php.net/manual/en/book.mysqli.php">MySQLi</a> (for MySQL):

```
$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Do something with $row
}
```

If you’re connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, `pg_prepare()` and `pg_execute()` for PostgreSQL). PDO is the universal option.
 
**Correctly setting up the connection**
 
Note that when using `PDO` to access a MySQL database real prepared statements are **not used by default**. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

```
$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
```

In the above example the error mode isn’t strictly necessary, **but it is advised to add it**. This way the script will not stop with a `Fatal Error` when something goes wrong. And it gives the developer the chance to `catch` any error(s) which are `throw`n as `PDOException`s.
 
What is **mandatory**, however, is the first `setAttribute()` line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren’t parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).
 
Although you can set the `charset` in the options of the constructor, it’s important to note that ‘older’ versions of PHP (before 5.3.6) <a href="https://www.php.net/manual/en/ref.pdo-mysql.connection.php">silently ignored the charset parameter</a> in the DSN.
 
**Explanation**
 
The SQL statement you pass to `prepare` is parsed and compiled by the database server. By specifying parameters (either a `?` or a named parameter like `:name` in the example above) you tell the database engine where you want to filter on. Then when you call `execute`, the prepared statement is combined with the parameter values you specify.
 
The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn’t intend.
 
Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the `$name` variable contains `'Sarah'; DELETE FROM employees` the result would simply be a search for the string `"'Sarah'; DELETE FROM employees"`, and you will not end up with <a href="https://xkcd.com/327/">an empty table</a>.
 
Another benefit of using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.
 
Oh, about how to do it for an insert, here’s an example (using PDO):

```
$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute([ 'column' => $unsafeValue ]);
```

**Can prepared statements be used for dynamic queries?**
 
While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.
 
For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

```
// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}
```

### 2. Should you use the datetime or timestamp data type in MySQL?

**Answer:**
 
Timestamps in MySQL are generally used to track changes to records and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.
 
If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way `("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)")` and it is simple to change the format of the value to a UNIX timestamp `("SELECT UNIX_TIMESTAMP(my_datetime)")` when you query the record if you want to operate on it with PHP.
 
**Alternative Answer:**
 
In MySQL 5 and above, **TIMESTAMP** values are converted from the current time zone to UTC for storage and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)
 
By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis, as described in <a href="https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html">MySQL Server Time Zone Support</a>.
 
### 3. How to import an SQL file using the command line in MySQL?

**Answer:**
 
You can try:

```
mysql -u username -p database_name < file.sql
```

Check <a href="https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html">MySQL Options</a>.
 
Note-1: It is better to use the full path of the SQL file `file.sql`.
 
Note-2: Use `-R` and `--triggers` to keep the routines and triggers of the original database. They are not copied by default.
 
Note-3: You may have to create the (empty) database from MySQL if it doesn’t exist already and the exported SQL doesn’t contain `CREATE DATABASE` (exported with `--no-create-db` or `-n` option) before you can import it.
 
**Alternative Answer:**
 
A common use of <a href="https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html">mysqldump</a> is for making a backup of an entire database:

```
shell> mysqldump db_name > backup-file.sql
```

You can load the dump file back into the server like this: **UNIX**

```
shell> mysql db_name < backup-file.sql
```

The same in **Windows** command prompt:

```
mysql -p -u [user] [database] < backup-file.sql
```

**PowerShell**

```
C:\> cmd.exe /c "mysql -u root -p db_name < backup-file.sql"
```

**MySQL command line**

```
mysql> use db_name;
mysql> source backup-file.sql;
```

### 4. How to get a list of user accounts using the command line in MySQL?

**Answer:**
 
You can use this query:

```
SELECT User FROM mysql.user;
```

Which will output a table like this:

```
+-------+
| User  |
+-------+
| root  |
+-------+
| user2 |
+-------+
```

**Alternative Answer:**
 
You can also do as shown below as it includes the host field which is important in MySQL to distinguish between user records.

```
select User,Host from mysql.user;
```

### 5. How to reset AUTO_INCREMENT in MySQL?

**Answer:**
 
You can reset the counter with:

```
ALTER TABLE tablename AUTO_INCREMENT = 1
```

For InnoDB you cannot set the `auto_increment` value lower or equal to the highest current index. (quote from <a href="https://www.viralpatel.net/reseting-mysql-autoincrement-column/">ViralPatel</a>):

> Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

### 6. Can we concatenate multiple MySQL rows into one field?

**Answer:**
 
You can use `GROUP_CONCAT`:

```
SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
```

You can add the `DISTINCT` operator to avoid duplicates:

```
SELECT person_id, GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies 
GROUP BY person_id;
```

You can also sort the values before imploding it using `ORDER BY`:

```
SELECT person_id, GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
```

There is a 1024 byte limit on the result. To solve this, run this query before your query:

```
SET group_concat_max_len = 2048;
```

Of course, you can change `2048` according to your needs. To calculate and assign the value:

```
SET group_concat_max_len = CAST(
    (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
    FROM peoples_hobbies 
    GROUP BY person_id)
    AS UNSIGNED
);
```

### 7. Which MySQL data type to use for storing boolean values?

**Answer:**
 
For MySQL 5.0.3 and higher, you can use `BIT`. The manual says:

> As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

Otherwise, according to the MySQL manual, you can use bool and boolean which are at the moment aliases of <a href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html">tinyint</a>:

> Bool, Boolean: These types are synonyms for TINYINT. A value of zero is considered false. Non-zero values are considered true.

MySQL also states that:

> We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html
 
### 8. How to output MySQL query results in CSV format?

**Answer:**
 
From http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

```
SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
```

Using this command columns names will not be exported.
 
Also note that `/var/lib/mysql-files/orders.csv` will be on the server that is running MySQL. The user that the MySQL process is running under must have permission to write to the directory chosen, or the command will fail.
 
If you want to write output to your local machine from a remote server (especially a hosted or virtualize machines such as Heroku or Amazon RDS), this solution is not suitable.
 
**Alternative Answer:**

```
$ mysql your_database --password=foo < my_requests.sql > out.csv
```

Which is tab-separated. Pipe it like that to get a true CSV:

```
... .sql | sed 's/\t/,/g' > out.csv
```

### 9. How to connect to a MySQL Database in Python?

**Answer:**
 
You can connect to MYSQL with Python 2 in three steps
 
**i. Setting**
 
You must install a MySQL driver before doing anything. Unlike PHP, Only the SQLite driver is installed by default with Python. The most used package to do so is <a href="https://pypi.org/project/MySQL-python/">MySQLdb</a> but it’s hard to install it using easy_install. Please note MySQLdb only supports Python 2.
 
For Windows users, you can get <a href="https://sourceforge.net/projects/mysql-python/files/">an exe of MySQLdb</a>.
 
For Linux, this is a casual package (python-mysqldb). (You can use `sudo apt-get install python-mysqldb` (for debian based distros), `yum install MySQL-python` (for rpm-based), or `dnf install python-mysql` (for modern fedora distro) in the command line to download.)
 
For Mac, you can <a href="https://stackoverflow.com/questions/1448429/how-to-install-mysqldb-python-data-access-library-to-mysql-on-mac-os-x#1448476">install MySQLdb using Macport</a>.
 
**ii. Usage**
 
After installing, Reboot. This is not mandatory, but it is to prevent something from going wrong. So please reboot.
 
Then it is just like using any other package:

```
#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect(host="localhost",    # your host, usually localhost
                     user="john",         # your username
                     passwd="megajonhy",  # your password
                     db="jonhydb")        # name of the data base

# you must create a Cursor object. It will let
#  you execute all the queries you need
cur = db.cursor()

# Use all the SQL you like
cur.execute("SELECT * FROM YOUR_TABLE_NAME")

# print all the first cell of all the rows
for row in cur.fetchall():
    print row[0]

db.close()
```

Of course, there are thousands of possibilities and options; this is a very basic example. You will have to look at the documentation. <a href="http://www.mikusa.com/python-mysql-docs/index.html">A good starting point</a>.
 
**iii. More advanced usage**
 
Once you know how it works, You may want to use an <a href="https://en.wikipedia.org/wiki/Object-Relational_Mapping">ORM</a> to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is <a href="https://www.sqlalchemy.org/">SQLAlchemy</a>.
 
You can also use another jewel in the Python world: <a href="https://peewee.readthedocs.io/en/latest/index.html">peewee</a>. It’s a very lite ORM, really easy and fast to setup then use. It makes your day for small projects or stand-alone apps, where using big tools like SQLAlchemy or Django is overkill:

```
import peewee
from peewee import *

db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')

class Book(peewee.Model):
    author = peewee.CharField()
    title = peewee.TextField()

    class Meta:
        database = db

Book.create_table()
book = Book(author="me", title='Peewee is cool')
book.save()
for book in Book.filter(author="me"):
    print book.title
```

This example works out of the box. Nothing other than having peewee (`pip install peewee`) is required.
 
**Alternative Answer:**
 
Here’s one way to do it, using <a href="https://pypi.org/project/MySQL-python/">MySQLdb</a>, which only supports Python 2:

```
#!/usr/bin/python
import MySQLdb

# Connect
db = MySQLdb.connect(host="localhost",
                     user="appuser",
                     passwd="",
                     db="onco")

cursor = db.cursor()

# Execute SQL select statement
cursor.execute("SELECT * FROM location")

# Commit your changes if writing
# In this case, we are only reading data
# db.commit()

# Get the number of rows in the resultset
numrows = cursor.rowcount

# Get and display one row at a time
for x in range(0, numrows):
    row = cursor.fetchone()
    print row[0], "-->", row[1]

# Close the connection
db.close()
```
 
### 10. What’s the difference between utf8_general_ci and utf8_unicode_ci?

**Answer:**
 
These two collations are both for the UTF-8 character encoding. The differences are in how text is sorted and compared.
 
**Note:** In MySQL, you have to use `utf8mb4` rather than `utf8`. Confusingly, `utf8` is a flawed UTF-8 implementation from early MySQL versions which remains only for backward compatibility. The fixed version was given the name `utf8mb4`.
 
Newer versions of MySQL have updated Unicode sorting rules, available under names such as `utf8mb4_0900_ai_ci` for equivalent rules based on Unicode 9.0 – and with no equivalent `_general` variant. People reading this now should probably use one of these newer collations instead of either `_unicode` or `_general`. Much of what’s written below is not of much interest anymore if you can use one of the newer collations instead.
 
**Key differences**

- `utf8mb4_unicode_ci` is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.
- `utf8mb4_general_ci` is a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations, such as when using particular languages or characters.

On modern servers, this performance boost will be all but negligible. It was devised in a time when servers had a tiny fraction of the CPU performance of today’s computers.

**Benefits of `utf8mb4_unicode_ci` over `utf8mb4_general_ci`**
 
`utf8mb4_unicode_ci`, which uses the Unicode rules for sorting and comparison, employs a fairly complex algorithm for correct sorting in a wide range of languages, and when using a wide range of special characters. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call ‘alphabetical order’.
 
As far as Latin (ie “European”) languages go, there is not much difference between the Unicode sorting and the simplified `utf8mb4_general_ci` sorting in MySQL, but there are still a few differences:

- For examples, the Unicode collation sorts “ß” like “ss”, and “Œ” like “OE” as people using those characters would normally want, whereas `utf8mb4_general_ci` sorts them as single characters (presumably like “s” and “e” respectively).
- Some Unicode characters are defined as ignorable, which means they shouldn’t count toward the sort order and the comparison should move on to the next character instead. `utf8mb4_unicode_ci` handles these properly.

In non-Latin languages, such as Asian languages or languages with different alphabets, there may be a lot more differences between Unicode sorting and simplified `utf8mb4_general_ci` sorting. The suitability of `utf8mb4_general_ci` will depend heavily on the language used. For some languages, it’ll be quite inadequate.
 
**What should you use?**
 
There is almost certainly no reason to use `utf8mb4_general_ci` anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.
 
In the past, some people recommended to use `utf8mb4_general_ci` except when accurate sorting was going to be important enough to justify the performance cost. Today, that performance cost has all but disappeared, and developers are treating internationalization more seriously.
 
There’s an argument to be made that if speed is more important to you than accuracy, you may as well not do any sorting at all. It’s trivial to make an algorithm faster if you do not need it to be accurate. So, `utf8mb4_general_ci` is a compromise that’s probably not needed for speed reasons and probably also not suitable for accuracy reasons.
 
One other thing is that even if you know your application only supports the English language, it may still need to deal with people’s names, which can often contain characters used in other languages in which it is just as important to sort correctly. Using the Unicode rules for everything helps add peace of mind that the very smart Unicode people have worked very hard to make sorting work properly.
 
**What the parts mean**
 
Firstly, `ci` is for case-insensitive sorting and comparison. This means it’s suitable for textual data, and case is not important. The other types of collation are `cs` (case-sensitive) for textual data where case is important, and `bin`, for where the encoding needs to match, bit for bit, which is suitable for fields which are really encoded binary data (including, for example, Base64). Case-sensitive sorting leads to some weird results and case-sensitive comparison can result in duplicate values differing only in letter case, so case-sensitive collations are falling out of favor for textual data – if case is significant to you, then otherwise ignorable punctuation and so on is probably also significant, and a binary collation might be more appropriate.
 
Next, `unicode` or `general` refers to the specific sorting and comparison rules – in particular, the way text is normalized or compared. There are many different sets of rules for the utf8mb4 character encoding, with `unicode` and `general` being two that attempt to work well in all possible languages rather than one specific one. The differences between these two sets of rules are the subject of this answer. Note that `unicode` uses rules from Unicode 4.0. Recent versions of MySQL add the rulesets `unicode_520` using rules from Unicode 5.2, and `0900` (dropping the “unicode_” part) using rules from Unicode 9.0.
 
And lastly, `utf8mb4` is of course the character encoding used internally. In this answer, it is only about Unicode based encodings.

### 11. How to take the backup of a single table in a MySQL database?

**Answer:**
 
Dump and restore a single table from .sql
 
**Dump**

```
mysqldump db_name table_name > table_name.sql
```

**Dumping from a remote database**

```
mysqldump -u <db_username> -h <db_host> -p db_name table_name > table_name.sql
```

For further reference: http://www.abbeyworkshop.com/howto/lamp/MySQL_Export_Backup/index.html
 
**Restore**

```
mysql -u <user_name> -p db_name
mysql> source <full_path>/table_name.sql
```

or in one line `mysql -u username -p db_name < /path/to/table_name.sql`
 
**Dump and restore a single table from a compressed (.sql.gz) format Dump**

```
mysqldump db_name table_name | gzip > table_name.sql.gz
```

**Restore**

```
gunzip < table_name.sql.gz | mysql -u username -p db_name
```

**Alternative Answer:**
 
<a href="https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html">mysqldump</a> can take a tbl_name parameter, so that it only backups the given tables.

```
mysqldump -u -p yourdb yourtable > c:\backups\backup.sql
```

### 12. How to use MySQL with Node.js?

**Answer:**
 
Check out the <a href="https://github.com/nodejs/node-v0.x-archive/wiki/modules#db-mysql">node.js module list</a>

- <a href="https://github.com/mysqljs/mysql">node-mysql</a> — A node.js module implementing the MySQL protocol
- <a href="https://github.com/sidorares/node-mysql2">node-mysql2</a> — Yet another pure JS async driver. Pipelining, prepared statements.
- <a href="https://github.com/Sannis/node-mysql-libmysqlclient">node-mysql-libmysqlclient</a> — MySQL asynchronous bindings based on libmysqlclient

node-mysql looks simple enough:

```
var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret',
});

connection.connect(function(err) {
  // connected! (unless `err` is set)
});
```

Queries:

```
var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
  // Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'
```

### 13. How to create a new user in MySQL and give it full access to one database?

**Answer:**
 
Try this to create the user:

```
CREATE USER 'user'@'hostname';
```

Try this to give it access to the database `dbTest`:

```
GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';
```

If you are running the code/site accessing MySQL on the same machine, the hostname would be localhost.
 
Now, the breakdown.
 
`GRANT` – This is the command used to create users and grant rights to databases, tables, etc.
 
`ALL PRIVILEGES` – This tells it the user will have all standard privileges. This does not include the privilege to use the GRANT command, however.
 
`dbtest.*` – This instructions MySQL to apply these rights for use in the entire dbtest database. You can replace the * with specific table names or store routines if you wish.
 
`TO 'user'@'hostname'` – ‘user’ is the username of the user account you are creating. **Note**: You must have the single quotes in there. ‘hostname’ tells MySQL what hosts the user can connect from. If you only want it from the same machine, use `localhost`
 
`IDENTIFIED BY 'password'` – As you would have guessed, this sets the password for that user.
 
**In Conclusion**
 
These are the 13 most commonly asked questions on MySQL. If you have any suggestions or any confusion, please comment below. If you need any help, we will be glad to help you.
 
We, at <a href="https://www.truemark.dev/">Truemark</a>, provide services like web and mobile app development, digital marketing, and website development. So, if you need any help and want to work with us, please feel free to contact us.
 
Hope this article helped you.

Original Source: <a href="https://thedevpost.com/blog/13-most-asked-questions-on-mysql/">DevPostbyTruemark</a>