MySQL FAQs

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 PDO (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 MySQLi (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()

pg_execute()

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

catch

throw

PDOExceptions

What is mandatory, however, is the first

setAttribute()

Although you can set the

charset

Explanation 

The SQL statement you pass to

prepare

?

:name

execute

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

 'Sarah'; DELETE FROM employees

"'Sarah'; DELETE FROM employees"

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)")

("SELECT UNIX_TIMESTAMP(my_datetime)")

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 MySQL Server Time Zone Support.

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 MySQL Options. 

Note-1: It is better to use the full path of the SQL file

file.sql

Note-2: Use

-R

--triggers

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

--no-create-db

-n

Alternative Answer: 

A common use of mysqldump 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

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

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

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

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 tinyint:

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

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 MySQLdb but it’s hard to install it using easy_install. Please note MySQLdb only supports Python 2. 

For Windows users, you can get an exe of MySQLdb. 

For Linux, this is a casual package (python-mysqldb). (You can use

sudo apt-get install python-mysqldb

yum install MySQL-python

dnf install python-mysql

For Mac, you can install MySQLdb using Macport. 

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 good starting point. 

iii. More advanced usage 

Once you know how it works, You may want to use an ORM to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is SQLAlchemy. 

You can also use another jewel in the Python world: peewee. 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

Alternative Answer: 

Here’s one way to do it, using MySQLdb, 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()

Reference here 

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

utf8

utf8 

utf8mb4

Newer versions of MySQL have updated Unicode sorting rules, available under names such as

utf8mb4_0900_ai_ci

_general

_unicode

_general

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

utf8mb4_general_ci

utf8mb4_unicode_ci

As far as Latin (ie “European”) languages go, there is not much difference between the Unicode sorting and the simplified

utf8mb4_general_ci

• 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_c

  i 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

utf8mb4_general_ci

What should you use?

There is almost certainly no reason to use

utf8mb4_general_ci 

In the past, some people recommended to use

utf8mb4_general_ci

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

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

cs

bin

Next,

unicode

general 

unicode

general 

unicode

unicode_520

0900

And lastly,

utf8mb4

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:

mysqldump 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 node.js module list

• node-mysql — A node.js module implementing the MySQL protocol
• node-mysql2 — Yet another pure JS async driver. Pipelining, prepared statements.
• node-mysql-libmysqlclient — 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

ALL PRIVILEGES 

dbtest.*

TO

'user'@'hostname'

localhost .

IDENTIFIED BY 'password'

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. 

Hope this article helped you.

This post was first published on DevPostbyTruemark.