mysql interview questions

Top mysql frequently asked interview questions

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)

MyISAM versus InnoDB

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. I don't need 100% accurate information at the time of read).
The task in question will be doing over 1 million database transactions an hour.

I've read a bunch of stuff on the web about the differences between MyISAM and InnoDB, and MyISAM seems like the obvious choice to me for the particular database/tables that I'll be using for this task. From what I seem to be reading, InnoDB is good if transactions are needed since row level locking is supported.

Does anybody have any experience with this type of load (or higher)? Is MyISAM the way to go?

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)

Should I use field 'datetime' or 'timestamp'?

Would you recommend using a datetime or a timestamp field, and why (using MySQL)?

I'm working with PHP on the server side.

Source: (StackOverflow)

What is the best collation to use for MySQL with PHP? [closed]

I'm wondering if there is a "best" choice for collation in MySQL for a general website where you aren't 100% of what will be entered? I understand that all the encodings should be the same, such as MySQL, Apache, the HTML and anything inside PHP.

In the past I have set PHP to output in "UTF-8", but which collation does this match in MySQL? I'm thinking it's one of the UTF-8 ones, but I have used utf8_unicode_ci, utf8_general_ci, and utf8_bin before.

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)


While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either:

  • ON DUPLICATE KEY UPDATE which implies an unnecessary update at some cost, or
  • INSERT IGNORE which implies an invitation for other kinds of failure to slip in unannounced.

Am I right in these assumptions? What's the best way to simply skip the rows that might cause duplicates and just continue on to the other rows?

Source: (StackOverflow)

How do you set a default value for a MySQL Datetime column?

How do you set a default value for a MySQL Datetime column?

In SQL Server it's getdate(). What is the equivalant for MySQL? I'm using MySQL 5.x if that is a factor.

Source: (StackOverflow)


I've got a messages table in MySQL which records messages between users. Apart from the typical ids and message types (all integer types) I need to save the actual message text as either VARCHAR or TEXT. I'm setting a front-end limit of 3000 characters which means the messages would never be inserted into the db as longer than this.

Is there a rationale for going with either VARCHAR(3000) or TEXT? There's something about just writing VARCHAR(3000) that feels somewhat counter-intuitive. I've been through other similar posts on Stack Overflow but would be good to get views specific to this type of common message storing.

Any help would be appreciated. Thanks.

Source: (StackOverflow)

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]

This question already has an answer here:

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Source: (StackOverflow)

Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like:

SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;

and get:


but instead I just want 1 row, 1 col:

shopping, fishing, coding

The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.

I've looked for a function on MySQL Doc and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets, so does anyone here know how to do this?

Source: (StackOverflow)