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+.

mysql
Filter by
Sorted by
Tagged with
3170 votes
40 answers
1.1m views

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.9k
2773 votes
27 answers
2.2m views

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']; ...
2765 votes
57 answers
5.1m views

How to 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.7k
2720 votes
14 answers
259k views

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
2176 votes
2 answers
2.2m views

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
  • 33.4k
1951 votes
12 answers
2.7m views

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.9k
1650 votes
27 answers
2.1m views

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
1628 votes
25 answers
1.9m views

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
  • 20.3k
1485 votes
16 answers
901k views

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
1431 votes
40 answers
1.6m views

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.3k
1416 votes
16 answers
1.4m views

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.7k
1387 votes
13 answers
1.1m views

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
1358 votes
13 answers
208k views

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.4k
1323 votes
9 answers
626k views

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.9k
1323 votes
34 answers
1.2m views

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
  • 27.2k
1281 votes
27 answers
1.4m views

How do I connect to a MySQL Database in Python?

How do I connect to a MySQL database using a python program?
user avatar
1196 votes
23 answers
3.0m views

MySQL error code: 1175 during UPDATE in MySQL Workbench

I want to update the column visited to give it the value 1. I tried this command in the SQL editor inside MySQL workbench: UPDATE tablename SET columnname=1; But I get an error that says: You are ...
Jury A's user avatar
  • 19.5k
1164 votes
13 answers
1.3m views

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.5k
1158 votes
21 answers
908k views

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.4k
1151 votes
46 answers
1.6m views

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 {...
1119 votes
12 answers
717k views

INNER JOIN ON vs WHERE clause

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.4k
1112 votes
20 answers
634k views

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
1088 votes
12 answers
1.1m views

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
  • 33.1k
1048 votes
15 answers
696k views

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

I have a table: table votes ( id, user, email, address, primary key(id), ); How can I make the columns user, email, address unique - i.e., ensure that there isn't any pair of rows ...
Niyaz's user avatar
  • 54.4k
1042 votes
11 answers
719k views

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.6k
1036 votes
28 answers
1.7m views

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
1005 votes
31 answers
776k views

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.9k
953 votes
12 answers
1.1m views

"INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

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
946 votes
4 answers
972k views

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
  • 12.1k
940 votes
27 answers
818k views

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
911 votes
22 answers
1.9m views

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,811
909 votes
9 answers
528k views

MySQL: Large VARCHAR vs. TEXT?

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.4k
892 votes
25 answers
305k views

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,241
877 votes
38 answers
1.7m views

Disable ONLY_FULL_GROUP_BY

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,868
877 votes
15 answers
1.1m views

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.8k
876 votes
30 answers
2.5m views

Host 'xxx.xx.xxx.xxx' 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.9k
856 votes
16 answers
844k views

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
  • 30.2k
841 votes
12 answers
627k views

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,887
837 votes
18 answers
756k views

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
826 votes
10 answers
894k views

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.9k
812 votes
11 answers
827k views

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,425
798 votes
19 answers
894k views

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
796 votes
17 answers
841k views

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
  • 59.4k
796 votes
11 answers
493k views

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
787 votes
13 answers
313k views

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.9k
785 votes
4 answers
312k views

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
  • 82.3k
782 votes
16 answers
662k views

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
772 votes
35 answers
1.1m views

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,825
763 votes
8 answers
1.6m views

'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
  • 14k
745 votes
10 answers
917k views

Create new user in MySQL and give it full access to one database

I want to create a new user in MySQL and give it full access only to one database, say dbTest that I create with a command like create database dbTest;. What would be the MySQL commands to do that?
jimgh's user avatar
  • 7,467

1
2 3 4 5
13240