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+.
662,316
questions
3171
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.
- 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'];
...
2768
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 ...
- 39.7k
2721
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 ...
- 174k
2180
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?
- 33.4k
1955
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 ...
- 25.9k
1652
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 ...
- 29.8k
1630
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.
- 20.3k
1485
votes
16
answers
903k
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, ...
- 32k
1433
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/ /,/...
- 22.3k
1417
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.
- 57.8k
1390
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 ...
Beat
1359
votes
13
answers
209k
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 ...
- 22.4k
1329
votes
9
answers
629k
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?
- 14k
1325
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 ...
- 27.2k
1281
votes
27
answers
1.5m
views
How do I connect to a MySQL Database in Python?
How do I connect to a MySQL database using a python program?
Marc Lincoln
1201
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 ...
- 19.6k
1165
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 ...
- 14.5k
1159
votes
21
answers
913k
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 ...
- 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
719k
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
...
- 16.4k
1113
votes
20
answers
637k
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 ...
- 157k
1090
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 ...
- 33.1k
1048
votes
15
answers
698k
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 ...
- 54.4k
1042
votes
11
answers
722k
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?
- 49.6k
1038
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.
- 36.5k
1005
votes
31
answers
777k
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 = $...
- 24.9k
954
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 ...
947
votes
4
answers
976k
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?
- 12.1k
943
votes
27
answers
821k
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?
- 48.5k
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,
...
- 9,811
910
votes
9
answers
529k
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 ...
- 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 ...
- 9,251
880
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?
- 21.9k
879
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?
- 9,888
877
votes
30
answers
2.6m
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 ...
- 31k
861
votes
16
answers
849k
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, ...
- 30.3k
843
votes
12
answers
629k
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:
...
- 8,907
838
votes
18
answers
759k
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....
- 77.6k
826
votes
9
answers
899k
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 ...
- 36.9k
814
votes
11
answers
833k
views
How to get size of mysql database?
How to get size of a mysql database?
Suppose the target database is called "v3".
- 8,445
798
votes
19
answers
896k
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 ...
- 12.8k
797
votes
17
answers
843k
views
MySQL: Get character-set of database or table or column?
What is the (default) charset for:
MySQL database
MySQL table
MySQL column
- 59.5k
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, ...
- 144k
788
votes
13
answers
314k
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 ...
- 27k
786
votes
4
answers
313k
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(...
- 82.4k
785
votes
16
answers
665k
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.
- 49.1k
773
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 = ...
- 7,835
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?
- 14k
746
votes
10
answers
920k
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?
- 7,477