Questions tagged [mysql]

MySQL is a free, open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). DO NOT USE this tag for other DBs such as SQL Server, SQLite etc. Those are different DBs that all use their own dialects of SQL to manage the data. Always specify the exact version of the server in the question. Versions 5.x differ greatly in their capabilities from versions 8+.

Filter by
Sorted by
Tagged with
3143 votes
40 answers

Should I use the datetime or timestamp data type in MySQL?

Would you recommend using a datetime or a timestamp field, and why (using MySQL)? I'm working with PHP on the server side.
Gad's user avatar
  • 41.6k
2773 votes
27 answers

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']; ...
2722 votes
56 answers

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C ...
Jaylen's user avatar
  • 39.1k
2707 votes
14 answers

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

What are the technical reasons for why one 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 ...
Madara's Ghost's user avatar
2147 votes
2 answers

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

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?
Lion King's user avatar
  • 32.9k
1903 votes
12 answers

Installing specific package version with pip

I am trying to install version 1.2.2 of MySQL_python, using a fresh virtualenv created with the --no-site-packages option. The current version shown in PyPi is 1.2.3. Is there a way to install the ...
Joe's user avatar
  • 25.4k
1625 votes
27 answers

SQL select only rows with max value on a column [duplicate]

I have this table for documents (simplified version here): id rev content 1 1 ... 2 1 ... 1 2 ... 1 3 ... How do I select one row per id and only the greatest rev? With the above data, the ...
Majid Fouladpour's user avatar
1599 votes
25 answers

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.
homerun's user avatar
  • 19.9k
1476 votes
16 answers

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; My Output: shopping fishing coding but instead I just want 1 row, 1 col: Expected Output: shopping, ...
Dean Rather's user avatar
  • 31.8k
1421 votes
40 answers

How can I 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/ /,/...
MCS's user avatar
  • 22.2k
1410 votes
16 answers

How can I get a list of user accounts using the command line in MySQL?

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.
burntsugar's user avatar
  • 57.4k
1373 votes
13 answers

Which MySQL data type to use for storing boolean values

Since MySQL doesn't seem to have any 'boolean' data type, which data type do you 'abuse' for storing true/false information in MySQL? Especially in the context of writing and reading from/to a PHP ...
user avatar
1343 votes
13 answers

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 this in the past on existing servers and always seem to end up having to fall back to ISO-8859-1. Where ...
mercutio's user avatar
  • 22.2k
1309 votes
34 answers

Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B ...
Vijay Dev's user avatar
  • 27k
1302 votes
9 answers

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?
KahWee Teng's user avatar
  • 13.7k
1276 votes
26 answers

How do I connect to a MySQL Database in Python?

How do I connect to a MySQL database using a python program?
user avatar
1176 votes
23 answers

MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: ...
Jury A's user avatar
  • 19.2k
1146 votes
12 answers

Insert into a MySQL table or update if exists

I want to add a row to a database table, but if a row exists with the same unique key I want to update the row. For example: INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19); Let’s ...
Keshan's user avatar
  • 14.3k
1136 votes
46 answers

How do I rename a MySQL database (change schema name)?

How do I quickly rename a MySQL database (change its schema name)? Usually I just dump a database and re-import it with a new name. This is not an option for very big databases. Apparently RENAME {...
1135 votes
19 answers

How to get the sizes of the tables of a MySQL database?

I can run this query to get the sizes of all tables in a MySQL database: show table status from myDatabaseName; I would like some help in understanding the results. I am looking for tables with the ...
JPashs's user avatar
  • 13.1k
1105 votes
12 answers


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 ...
JCCyC's user avatar
  • 16.2k
1091 votes
20 answers

Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for ...
Your Common Sense's user avatar
1074 votes
11 answers

How can I do 'insert if not exists' in MySQL?

I started by googling and found the article How to write INSERT if NOT EXISTS queries in standard SQL which talks about mutex tables. I have a table with ~14 million records. If I want to add more ...
warren's user avatar
  • 32.7k
1039 votes
14 answers

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

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 ...
Niyaz's user avatar
  • 54k
1035 votes
26 answers

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.
Brian Boatright's user avatar
1029 votes
11 answers

How can I 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 database and list out all tables which have those columns. Is there any easy script?
Jobi Joy's user avatar
  • 49.2k
1002 votes
31 answers

mysql_fetch_array()/mysql_fetch_assoc()/mysql_fetch_row()/mysql_num_rows etc... expects parameter 1 to be resource

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 This is my code: $username = $...
iamjonesy's user avatar
  • 24.8k
946 votes
12 answers


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 ...
Thomas G Henry LLC's user avatar
932 votes
4 answers

TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes

Per the MySQL docs, there are four TEXT types: TINYTEXT TEXT MEDIUMTEXT LONGTEXT What is the maximum length that I can store in a column of each data type assuming the character encoding is UTF-8?
Lalith B's user avatar
  • 11.9k
927 votes
27 answers

Finding duplicate values in MySQL

I have a table with a varchar column, and I would like to find all the records that have duplicate values in this column. What is the best query I can use to find the duplicates?
Jon Tackabury's user avatar
908 votes
22 answers

How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?

I have a table of player performance: CREATE TABLE TopTen ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, home INT UNSIGNED NOT NULL, `datetime`DATETIME NOT NULL, player VARCHAR(6) NOT NULL, ...
Kaptah's user avatar
  • 9,781
899 votes
9 answers


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 ...
Tom's user avatar
  • 30.1k
892 votes
25 answers

MyISAM versus InnoDB [closed]

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 ...
user2013's user avatar
  • 9,231
866 votes
38 answers


I accidentally enabled ONLY_FULL_GROUP_BY mode like this: SET sql_mode = 'ONLY_FULL_GROUP_BY'; How do I disable it?
ZviBar's user avatar
  • 9,758
863 votes
15 answers

How can I do a FULL OUTER JOIN in MySQL?

I want to do a full outer join in MySQL. Is this possible? Is a full outer join supported by MySQL?
Spencer's user avatar
  • 21.4k
859 votes
29 answers

Host '' is not allowed to connect to this MySQL server

This should be dead simple, but I cannot get it to work for the life of me. I'm just trying to connect remotely to my MySQL server. Connecting as: mysql -u root -h localhost -p works fine, but ...
concept47's user avatar
  • 30.3k
835 votes
15 answers

How to truncate a foreign key constrained table?

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, ...
user391986's user avatar
  • 29.6k
832 votes
15 answers

Duplicating a MySQL table, indices, and data

How do I copy or clone or duplicate the data, structure, and indices of a MySQL table to a new one? This is what I've found so far. This will copy the data and the structure, but not the indices: ...
xkcd150's user avatar
  • 8,797
817 votes
17 answers

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category....
Sergio del Amo's user avatar
808 votes
10 answers

How can I temporarily disable a foreign key constraint in MySQL?

Is it possible to temporarily disable constraints in MySQL? I have two Django models, each with a foreign key to the other one. Deleting instances of a model returns an error because of the foreign ...
jul's user avatar
  • 36.4k
792 votes
11 answers

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

Is there a collation type which is officially recommended by MySQL, for a general website where you aren't 100% sure of what will be entered? I understand that all the encodings should be the same, ...
Darryl Hein's user avatar
789 votes
18 answers

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE ...
Fernando Barrocal's user avatar
788 votes
17 answers

MySQL: Get character-set of database or table or column?

What is the (default) charset for: MySQL database MySQL table MySQL column
Amandasaurus's user avatar
  • 58.4k
782 votes
10 answers

How to get size of mysql database?

How to get size of a mysql database? Suppose the target database is called "v3".
Newbie's user avatar
  • 8,115
774 votes
4 answers

SQL injection that gets around mysql_real_escape_string()

Is there an SQL injection possibility even when using mysql_real_escape_string() function? Consider this sample situation. SQL is constructed in PHP like this: $login = mysql_real_escape_string(...
Richard Knop's user avatar
  • 81.2k
773 votes
13 answers

When to use single quotes, double quotes, and backticks in MySQL

I am trying to learn the best way to write queries. I also understand the importance of being consistent. Until now, I have randomly used single quotes, double quotes, and backticks without any real ...
Nate's user avatar
  • 26.3k
762 votes
16 answers

How do I see all foreign keys to a table or column?

In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as this Oracle question, but for MySQL.
Christian Oudard's user avatar
762 votes
8 answers

'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?
Michael's user avatar
  • 13.9k
743 votes
35 answers

MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

I can't make a simple connection to the server for some reason. I install the newest MySQL Community 8.0 database along with Node.JS with default settings. This is my node.js code var mysql = ...
Pig 's user avatar
  • 7,535
738 votes
28 answers

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 ...
Chris Bartow's user avatar
  • 14.9k

2 3 4 5