mysql interview questions
Top mysql frequently asked interview questions
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)
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)
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)
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
EOQ
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)
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)
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)
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)
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)
Between utf8_general_ci
and utf8_unicode_ci
, are there any differences in terms of performance?
Source: (StackOverflow)
I am trying to select data from
a MySQL table, but I get one of the following error messages:
mysql_fetch_array() expects parameter 1 to be resource, boolean given
or
mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given
This is my code:
$username = $_POST['username'];
$password = $_POST['password'];
$result = mysql_query('SELECT * FROM Users WHERE UserName LIKE $username');
while($row = mysql_fetch_array($result)) {
echo $row['FirstName'];
}
The same applies to code like
$result = mysqli_query($mysqli, 'SLECT ...');
// mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given
while( $row=mysqli_fetch_array($result) ) {
...
and
$result = $mysqli->query($mysqli, 'SELCT ...');
// Call to a member function fetch_assoc() on a non-object
while( $row=$result->fetch_assoc($result) ) {
...
and
$result = $pdo->query('SLECT ...', PDO::FETCH_ASSOC);
// Invalid argument supplied for foreach()
foreach( $result as $row ) {
...
and
$stmt = $mysqli->prepare('SLECT ...');
// Call to a member function bind_param() on a non-object
$stmt->bind_param(...)
and
$stmt = $pdo->prepare('SLECT ...');
// Call to a member function bindParam() on a non-object
$stmt->bindParam(...)
Source: (StackOverflow)
Since MySQL doesn't seem to have any 'boolean' datatype, which datatype do you 'abuse' for storing true/false information in MySQL? Especially in the context of writing and reading from/to a PHP-Script.
Over time I have used and seen several approaches:
- tinyint, varchar fields containing the values 0/1,
- varchar fields containing the strings '0'/'1' or 'true'/'false'
- and finally enum Fields containing the two options 'true'/'false'.
None of the above seems optimal, I tend to prefer the tinyint 0/1 variant, since automatic type conversion in PHP gives me boolean values rather simply.
So which datatype do you use, is there a type designed for boolean values which I have overlooked? Do you see any advantages/disadvantages by using one type or another?
Source: (StackOverflow)
As I continue to build more and more websites and web applications I am often asked to store user's passwords in a way that they can be retrieved if/when the user has an issue (either to email a forgotten password link, walk them through over the phone, etc.) When I can I fight bitterly against this practice and I do a lot of ‘extra’ programming to make password resets and administrative assistance possible without storing their actual password.
When I can’t fight it (or can’t win) then I always encode the password in some way so that it at least isn’t stored as plaintext in the database—though I am aware that if my DB gets hacked that it won’t take much for the culprit to crack the passwords as well—so that makes me uncomfortable.
In a perfect world folks would update passwords frequently and not duplicate them across many different sites—unfortunately I know MANY people that have the same work/home/email/bank password, and have even freely given it to me when they need assistance. I don’t want to be the one responsible for their financial demise if my DB security procedures fail for some reason.
Morally and ethically I feel responsible for protecting what can be, for some users, their livelihood even if they are treating it with much less respect.
I am certain that there are many avenues to approach and arguments to be made for salting hashes and different encoding options, but is there a single ‘best practice’ when you have to store them? In almost all cases I am using PHP and MySQL if that makes any difference in the way I should handle the specifics.
Additional Information for Bounty
I want to clarify that I know this is not something you want to have to do and that in most cases refusal to do so is best. I am, however, not looking for a lecture on the merits of taking this approach I am looking for the best steps to take if you do take this approach.
In a note below I made the point that websites geared largely toward the elderly, mentally challenged, or very young can become confusing for people when they are asked to perform a secure password recovery routine. Though we may find it simple and mundane in those cases some users need the extra assistance of either having a service tech help them into the system or having it emailed/displayed directly to them.
In such systems the attrition rate from these demographics could hobble the application if users were not given this level of access assistance, so please answer with such a setup in mind.
Thanks to Everyone
This has been a fun questions with lots of debate and I have enjoyed it. In the end I selected an answer that both retains password security (I will not have to keep plain text or recoverable passwords), but also makes it possible for the user base I specified to log into a system without the major drawbacks I have found from normal password recovery.
As always there were about 5 answers that I would like to have marked correct for different reasons, but I had to choose the best one--all the rest got a +1. Thanks everyone!
Also, thanks to everyone in the Stack community who voted for this question and/or marked it as a favorite. I take hitting 100 up votes as a compliment and hope that this discussion has helped someone else with the same concern that I had.
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)