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
661,990
questions
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.
- 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 ...
- 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 ...
- 174k
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?
- 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 ...
- 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 ...
- 29.8k
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.
- 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, ...
- 32k
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/ /,/...
- 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.
- 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 ...
Beat
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 ...
- 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?
- 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 ...
- 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?
Marc Lincoln
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 ...
- 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 ...
- 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 ...
- 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
...
- 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 ...
- 158k
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 ...
- 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 ...
- 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?
- 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.
- 36.5k
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 = $...
- 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 ...
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?
- 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?
- 48.4k
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
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 ...
- 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,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?
- 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?
- 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 ...
- 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, ...
- 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:
...
- 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....
- 77.5k
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 ...
- 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".
- 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 ...
- 12.8k
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
- 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, ...
- 144k
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 ...
- 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(...
- 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.
- 49k
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 = ...
- 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?
- 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?
- 7,467