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: 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: Use prepared statements and parameterized queries. i. Using (for any supported database driver): PDO $stmt = $pdo->prepare( ); $stmt->execute([ => $name ]); foreach ($stmt $row) { } 'SELECT * FROM employees WHERE name = :name' 'name' as // Do something with $row ii. Using (for MySQL): MySQLi $stmt = $dbConnection->prepare( ); $stmt->bind_param( , $name); $stmt->execute(); $result = $stmt->get_result(); ($row = $result->fetch_assoc()) { } 'SELECT * FROM employees WHERE name = ?' 's' // 's' specifies the variable type => 'string' while // 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, and for PostgreSQL). PDO is the universal option. pg_prepare() pg_execute() Correctly setting up the connection Note that when using PDO to access a MySQL database prepared statements are . To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is: real not used by default $dbConnection = PDO( , , ); $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, ); $dbConnection->setAttribute(PDO::ATTR_ERRMODE, ::ERRMODE_EXCEPTION); new 'mysql:dbname=dbtest;host=127.0.0.1;charset=utf8' 'user' 'password' false PDO In the above example the error mode isn’t strictly necessary, . This way the script will not stop with a when something goes wrong. And it gives the developer the chance to any error(s) which are n as . but it is advised to add it Fatal Error catch throw PDOExceptions What is , however, is the first line, which tells PDO to disable emulated prepared statements and use 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). mandatory setAttribute() real Although you can set the in the options of the constructor, it’s important to note that ‘older’ versions of PHP (before 5.3.6) in the DSN. charset silently ignored the charset parameter Explanation The SQL statement you pass to is parsed and compiled by the database server. By specifying parameters (either a or a named parameter like in the example above) you tell the database engine where you want to filter on. Then when you call , the prepared statement is combined with the parameter values you specify. 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 variable contains the result would simply be a search for the string , and you will not end up with . $name 'Sarah'; DELETE FROM employees "'Sarah'; DELETE FROM employees" 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( ); $preparedStatement->execute([ => $unsafeValue ]); 'INSERT INTO table (column) VALUES (:column)' 'column' 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. (empty($dir) || $dir !== ) { $dir = ; } // Value whitelist // $dir can only be 'DESC', otherwise it will be 'ASC' if 'DESC' '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 and it is simple to change the format of the value to a UNIX timestamp when you query the record if you want to operate on it with PHP. ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") ("SELECT UNIX_TIMESTAMP(my_datetime)") Alternative Answer: In MySQL 5 and above, 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 for other types such as DATETIME.) TIMESTAMP not 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? You can try: Answer: mysql -u username -p database_name < file.sql Check . MySQL Options It is better to use the full path of the SQL file . Note-1: file.sql Use and to keep the routines and triggers of the original database. They are not copied by default. Note-2: -R --triggers You may have to create the (empty) database from MySQL if it doesn’t exist already and the exported SQL doesn’t contain (exported with or option) before you can import it. Note-3: CREATE DATABASE --no-create-db -n Alternative Answer: A common use of is for making a backup of an entire database: mysqldump shell> mysqldump db_name > backup-file.sql You can load the dump file back into the server like this: UNIX shell> mysql db_name cmd.exe /c "mysql -u root -p db_name 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 mysql.user; from 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 value lower or equal to the highest current index. (quote from ): auto_increment 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. Can we concatenate multiple MySQL rows into one field? 6. Answer: You can use : GROUP_CONCAT SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ) FROM peoples_hobbies GROUP BY person_id; ', ' You can add the operator to avoid duplicates: 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 according to your needs. To calculate and assign the value: 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 . The manual says: 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 . A value of zero is considered false. Non-zero values are considered true. TINYINT 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 = INTO OUTFILE FIELDS TERMINATED BY ENCLOSED BY LINES TERMINATED BY ; 'bar' '/var/lib/mysql-files/orders.csv' ',' '"' '\n' Using this command columns names will not be exported. Also note that will be on the 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. /var/lib/mysql-files/orders.csv server 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 out.csv Which is tab-separated. Pipe it like that to get a true CSV: ... .sql | sed > out.csv 's/\t/,/g' 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. MySQLdb For Windows users, you can get an . exe of MySQLdb For Linux, this is a casual package (python-mysqldb). (You can use (for debian based distros), (for rpm-based), or (for modern fedora distro) in the command line to download.) 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: MySQLdb db = MySQLdb.connect(host= , # your host, usually localhost user= , # your username passwd= , # your password db= ) # name the data base # you must create a Cursor object. It will # you execute all the queries you need cur = db.cursor() # Use all the SQL you like cur.execute( ) # print all the first cell all the rows row cur.fetchall(): print row[ ] db.close() #!/usr/bin/python import "localhost" "john" "megajonhy" "jonhydb" of let "SELECT * FROM YOUR_TABLE_NAME" of for in 0 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 to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is . ORM SQLAlchemy 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: peewee peewee peewee * db = MySQLDatabase( , user= , passwd= ) = peewee.CharField() title = peewee.TextField() = db Book.create_table() book = Book(author= , title= ) book.save() book Book.filter(author= ): print book.title import from import 'jonhydb' 'john' 'megajonhy' ( . ): class Book peewee Model author : class Meta database "me" 'Peewee is cool' for in "me" This example works out of the box. Nothing other than having peewee ( ) is required. pip install peewee Alternative Answer: Here’s one way to do it, using , which only supports Python 2: MySQLdb MySQLdb # Connect db = MySQLdb.connect(host= , user= , passwd= , db= ) cursor = db.cursor() # Execute SQL select statement cursor.execute( ) # Commit your changes writing # In , we are only reading data # db.commit() # Get the number rows the resultset numrows = cursor.rowcount # Get and display one row at a time x range( , numrows): row = cursor.fetchone() print row[ ], , row[ ] # Close the connection db.close() #!/usr/bin/python import "localhost" "appuser" "" "onco" "SELECT * FROM location" if this case of in for in 0 0 "-->" 1 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. In MySQL, you have to use rather than . Confusingly, is a flawed UTF-8 implementation from early MySQL versions which remains only for backward compatibility. The fixed version was given the name . Note: utf8mb4 utf8 utf8 utf8mb4 Newer versions of MySQL have updated Unicode sorting rules, available under names such as for equivalent rules based on Unicode 9.0 – and with no equivalent variant. People reading this now should probably use one of these newer collations instead of either or . Much of what’s written below is not of much interest anymore if you can use one of the newer collations instead. utf8mb4_0900_ai_ci _general _unicode _general Key differences is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages. utf8mb4_unicode_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. utf8mb4_general_ci 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 , 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’. utf8mb4_unicode_ci As far as Latin (ie “European”) languages go, there is not much difference between the Unicode sorting and the simplified sorting in MySQL, but there are still a few differences: utf8mb4_general_ci For examples, the Unicode collation sorts “ß” like “ss”, and “Œ” like “OE” as people using those characters would normally want, whereas sorts them as single characters (presumably like “s” and “e” respectively). utf8mb4_general_ci 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. i handles these properly. utf8mb4_unicode_c In non-Latin languages, such as Asian languages or languages with different alphabets, there may be a lot differences between Unicode sorting and simplified sorting. The suitability of will depend heavily on the language used. For some languages, it’ll be quite inadequate. more utf8mb4_general_ci utf8mb4_general_ci What should you use? There is almost certainly no reason to use 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. utf8mb4_general_ci In the past, some people recommended to use 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. 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, is a compromise that’s probably not needed for speed reasons and probably also not suitable for accuracy reasons. 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, is for sorting and comparison. This means it’s suitable for textual data, and case is not important. The other types of collation are (case-sensitive) for textual data where case is important, and , 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. ci case-insensitive cs bin Next, or 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 and 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 uses rules from Unicode 4.0. Recent versions of MySQL add the rulesets using rules from Unicode 5.2, and (dropping the “unicode_” part) using rules from Unicode 9.0. unicode general unicode general unicode unicode_520 0900 And lastly, is of course the character encoding used internally. In this answer, it is only about Unicode based encodings. 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 -h -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 -p db_name mysql> source /table_name.sql or in one line mysql -u username -p db_name table_name.sql.gz Restore gunzip c:\backups\backup.sql 12. How to use MySQL with Node.js? Answer: Check out the node.js module list — A node.js module implementing the MySQL protocol node-mysql — Yet another pure JS async driver. Pipelining, prepared statements. node-mysql2 — MySQL asynchronous bindings based on libmysqlclient node-mysql-libmysqlclient node-mysql looks simple enough: mysql = ( ); connection = mysql.createConnection({ : , : , : , }); connection.connect( { }); var require 'mysql' var host 'example.org' user 'bob' password 'secret' ( ) function err // connected! (unless `err` is set) Queries: post = { : , : }; query = connection.query( , post, { }); .log(query.sql); var id 1 title 'Hello MySQL' var 'INSERT INTO posts SET ?' ( ) function err, result // Neat! console // 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 @ IDENTIFIED BY ; 'user' 'hostname' 'password' If you are running the code/site accessing MySQL on the same machine, the hostname would be localhost. Now, the breakdown. – This is the command used to create users and grant rights to databases, tables, etc. GRANT – This tells it the user will have all standard privileges. This does not include the privilege to use the GRANT command, however. ALL PRIVILEGES – 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. dbtest.* TO – ‘user’ is the username of the user account you are creating. 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 'user'@'hostname' Note: localhost . – As you would have guessed, this sets the password for that user. IDENTIFIED BY 'password' 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. In Conclusion This post was first published on DevPostbyTruemark .