mysql interview questions
Top mysql frequently asked interview questions
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)
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)
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:
JIM JONES 100 MAIN ST
JOHN SMITH 100 MAIN ST
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)
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)
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)
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:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1, table2
WHERE
table1.foreignkey = table2.primarykey
AND (some other conditions)
Or else:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1 INNER JOIN table2
ON table1.foreignkey = table2.primarykey
WHERE
(some other conditions)
Are those two worked on the same way by MySQL?
Source: (StackOverflow)
I have a table:
table votes (
id,
user,
email,
address,
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?
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)
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)
Using MySQL, I can do something like:
SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;
and get:
shopping
fishing
coding
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)