13 Most Asked Questions On MySQL

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() 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 thrown 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) silently ignored the charset parameter 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 an empty table.

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 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 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 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 value lower or equal to the highest current index. (quote from ViralPatel):

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 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 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 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 (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 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) is required.

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

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:

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 – 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 Truemark, 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: DevPostbyTruemark