mysql interview questions

Top mysql frequently asked interview questions

How to reset AUTO_INCREMENT in MySQL?

How can I reset the auto-increment of a field? I want it to start counting from 1 again.

Source: (StackOverflow)

Would you recommend PostgreSQL over MySQL? [closed]

We are currently working with JavaEE and MySQL 5 in our company, but we have some queries, especially delete queries that take > 10 min to execute. We are considering a switch to PostgreSQL.

What are the advantages of PostgreSQL over MySQL, if there are any? Do you have experience with both DBs and give me a roundup about whether this is a good idea or does this fully depend on the needs of our server?

Source: (StackOverflow)

How to find all the tables in MySQL with specific column names in them?

I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy script?

Source: (StackOverflow)


For simplicity, assume all relevant fields are NOT NULL.

You can do:

    table1.this, table2.that, table2.somethingelse
    table1, table2
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

Or else:

    table1.this, table2.that, table2.somethingelse
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
    (some other conditions)

Are those two worked on the same way by MySQL?

Source: (StackOverflow)

How do I specify unique constraint for multiple columns in MySQL?

I have a table:

table votes (
    primary key(id),

Now I want to make the columns user, email, address unique (together).

How do I do this in MySql?

  • Of course the example is just... an example. So please don't worry about the semantics.

Source: (StackOverflow)

How to output MySQL query results in CSV format?

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format?

Here's what I'm doing now:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students

It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.

Source: (StackOverflow)

UTF-8 all the way through

I'm setting up a new server, and want to support UTF-8 fully in my web application. I have tried in the past on existing servers and always seem to end up having to fall back to ISO-8859-1.

Where exactly do I need to set the encoding/charsets? I'm aware that I need to configure Apache, MySQL and PHP to do this - is there some standard checklist I can follow, or perhaps troubleshoot where the mismatches occur?

This is for a new Linux server, running MySQL 5, PHP 5 and Apache 2.

Source: (StackOverflow)

How to get a list of MySQL user accounts

I'm using the MySQL command line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this?

I'm using MySQL version 5.4.1.

Source: (StackOverflow)

Why shouldn't I use mysql_* functions in PHP?

What are the technical reasons why I shouldn't use mysql_* functions? (e.g. mysql_query(), mysql_connect() or mysql_real_escape_string())?

Why should I use something else even if they work on my site?

Source: (StackOverflow)

How can I prevent SQL-injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input']; 

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

What can be done to prevent this from happening?

Source: (StackOverflow)

What's the difference between utf8_general_ci and utf8_unicode_ci

Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance?

Source: (StackOverflow)

How do I connect to a MySQL Database in Python?

How do I connect to a MySQL database using a python program?

Source: (StackOverflow)

Find duplicate records in MySQL

I want to pull out duplicate records in a MySQL Database. This can be done with:

SELECT address, count(id) as cnt FROM list
GROUP BY address HAVING cnt > 1

Which results in:

100 MAIN ST    2

I would like to pull it so that it shows each row that is a duplicate. Something like:


Any thoughts on how this can be done? I'm trying to avoid doing the first one then looking up the duplicates with a second query in the code.

Source: (StackOverflow)

Installing specific package versions with pip

I'm trying to install version 1.2.2 of the MySQL_python adaptor. The current version shown in PyPi is 1.2.3. Is there a way to install the older version? I found an article stating that this should do it:

pip install MySQL_python==1.2.2

When installed, however, it still shows MySQL_python-1.2.3-py2.6.egg-info in the site packages. Is this a problem specific to this package, or am I doing something wrong?

Source: (StackOverflow)

How to shrink/purge ibdata1 file in MySQL

I am using MySQL in localhost as a "query tool" for performing statistics in R, that is, everytime I run a R script, I create a new database (A), create a new table (B), import the data into B, submit a query to get what I need, and then I drop B and drop A.

It's working fine for me, but I realize that the ibdata file size is increasing rapidly, I stored nothing in MySQL, but the ibdata1 file already exceeded 100 MB.

I am using more or less default MySQL setting for the setup, is there a way for I can automatically shrink/purge the ibdata1 file after a fixed period of time?

Source: (StackOverflow)