207 reads

MySQL FAQs

by KiranSeptember 26th, 2020

Too Long; Didn't Read

13 most asked questions on open-source relational database management system. Prepared statements and parameterized queries can be used to prevent SQL injection in PHP. Timers are generally used to track changes to records and are generally updated every time a record is changed. If you want to store a specific field, you should use a UNIX timestamp or a native format of the format of a query field to store the value you meant to use, such as a date or a time-to-date field.

Companies Mentioned

Coin Mentioned

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 (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 (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

PDOExceptions

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

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 .

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 .

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

-n

option) before you can import it.

Alternative Answer:

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

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 :

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

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 :

Bool, Boolean: These types are synonyms for . 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:

8. How to output MySQL query results in CSV format?

Answer:

From

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 but it’s hard to install it using easy_install. Please note MySQLdb only supports Python 2. For Windows users, you can get an .

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 .

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

iii. More advanced usage

Once you know how it works, You may want to use an to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is . You can also use another jewel in the Python world: . 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 , 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 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

_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_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

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

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

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

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:

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

'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.

Hope this article helped you.

This post was first published on .

L O A D I N G
. . . comments & more!