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. Note that configuration/setup-related questions are off-topic on stackoverflow.
mysql
662,061
questions
3182
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.
- 42.1k
2787
votes
57
answers
5.2m
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 ...
- 40k
2769
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'];
...
2722
votes
14
answers
260k
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
2183
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.5k
1981
votes
12
answers
2.8m
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 ...
- 26.2k
1662
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 ...
- 30k
1641
votes
27
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.5k
1488
votes
16
answers
910k
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, ...
- 32.1k
1437
votes
40
answers
1.7m
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.4k
1416
votes
17
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
1398
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
211k
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
1334
votes
9
answers
639k
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
1334
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.3k
1283
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
1212
votes
23
answers
3.1m
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.7k
1171
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.6k
1164
votes
21
answers
933k
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.6k
1156
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 {...
1125
votes
20
answers
646k
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
1120
votes
12
answers
722k
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
1095
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.2k
1049
votes
15
answers
706k
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.5k
1049
votes
11
answers
732k
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.8k
1044
votes
27
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.6k
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 = $...
- 25k
959
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 ...
951
votes
4
answers
989k
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
948
votes
27
answers
833k
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.7k
914
votes
9
answers
533k
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.5k
912
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,821
892
votes
25
answers
306k
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
884
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,948
883
votes
14
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?
- 22k
881
votes
22
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 ...
- 31.2k
865
votes
16
answers
864k
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.5k
854
votes
12
answers
637k
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:
...
- 9,017
839
votes
18
answers
770k
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.7k
838
votes
9
answers
923k
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 ...
- 37.2k
815
votes
11
answers
856k
views
How to get size of mysql database?
How to get size of a mysql database?
Suppose the target database is called "v3".
- 8,455
803
votes
17
answers
854k
views
MySQL: Get character-set of database or table or column?
What is the (default) charset for:
MySQL database
MySQL table
MySQL column
- 59.8k
800
votes
19
answers
903k
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.9k
796
votes
16
answers
677k
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.4k
796
votes
11
answers
494k
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
794
votes
13
answers
319k
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 ...
- 27.4k
788
votes
4
answers
315k
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.8k
782
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,925
765
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?
- 14.1k
749
votes
10
answers
931k
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,507