Stack Overflow
Stack Overflow

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 is an Open Source RDBMS (Relational Database Management System) that runs as a server providing multi-user access to several databases. Its source code is available under the GNU GPL (General Public License). MySQL was acquired by Oracle in 2009.

MariaDB is a fork of MySQL using the same dialect of SQL and offering most of the same functions. See for fork-specific questions and answers.

MySQL offers standard database driver connectivity for using MySQL with applications and tools compatible with industry standards ODBC and JDBC.

How to ask a good and answerable question

Please see this post: Tips for asking a good Structured Query Language (SQL) question to get tips on how to ask a question about MySQL. Following some of these steps will help people to help you solve your problem.

Minimal, reproducible examples help the Stack Overflow community give good answers. Please read Why should I provide a Minimal Reproducible Example for a very simple SQL query?.

Always specify the exact version of the server in the question. Versions 5.x differ greatly in their capabilities from versions 8+.

Pronunciation

Officially it is /maɪˌɛskjuːˈɛl/ ("My S-Q-L"), but is often pronounced /maɪˈsiːkwəl/ ("My Sequel").

Latest Version (MySQL Community Server 8.0.27) and Version History

Example

SELECT * FROM users ORDER BY name

Resources

MySQL Tutorials

Popular questions

Popular administration tools

Chat Room

Related tags

661202 questions
772
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 thought. Example: $query = 'INSERT INTO table (id,…
Nate
  • 26,164
  • 34
  • 130
  • 214
761
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
  • 13,838
  • 18
  • 52
  • 81
757
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
  • 48,140
  • 25
  • 66
  • 69
740
votes
34 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 = require('mysql'); var con =…
Pig
  • 7,505
  • 3
  • 10
  • 14
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 would like to pull it so that it shows each row that…
Chris Bartow
  • 14,873
  • 11
  • 43
  • 46
734
votes
9 answers

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
  • 7,357
  • 3
  • 16
  • 3
696
votes
11 answers

MySQL: Grant **all** privileges on database

I've created database, for example 'mydb'. CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin; CREATE USER 'myuser'@'%' IDENTIFIED BY PASSWORD '*HASH'; GRANT ALL ON mydb.* TO 'myuser'@'%'; GRANT ALL ON mydb TO 'myuser'@'%'; GRANT CREATE ON…
marioosh
  • 27,328
  • 49
  • 143
  • 192
675
votes
13 answers

MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the starting date-time for other…
John M
  • 14,338
  • 29
  • 91
  • 143
671
votes
11 answers

How to skip certain database tables with mysqldump?

Is there a way to restrict certain tables from the mysqldump command? For example, I'd use the following syntax to dump only table1 and table2: mysqldump -u username -p database table1 table2 > database.sql But is there a similar way to dump all…
Zac
  • 1,010
  • 3
  • 11
  • 20
667
votes
9 answers

How do I change the data type for a column in MySQL?

I want to change the data type of multiple columns from float to int. What is the simplest way to do this? There is no data to worry about, yet.
Eric Wilson
  • 57,719
  • 77
  • 200
  • 270
666
votes
38 answers

Authentication plugin 'caching_sha2_password' cannot be loaded

I am connecting MySQL - 8.0 with MySQL Workbench and getting the below error: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found I have tried with…
Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
663
votes
37 answers

MySQL Error #1071 - Specified key was too long; max key length is 767 bytes

When I executed the following command: ALTER TABLE `mytable` ADD UNIQUE ( `column1` , `column2` ); I got this error message: #1071 - Specified key was too long; max key length is 767 bytes Information about column1 and column2: column1 varchar(20)…
Steven
  • 24,410
  • 42
  • 108
  • 130
661
votes
3 answers

Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

What are the differences between PRIMARY, UNIQUE, INDEX and FULLTEXT when creating MySQL tables? How would I use them?
Sam
654
votes
19 answers

How do I restore a dump file from mysqldump?

I was given a MySQL database file that I need to restore as a database on my Windows Server 2008 machine. I tried using MySQL Administrator, but I got the following error: The selected file was generated by mysqldump and cannot be restored by …
Zack Peterson
  • 56,055
  • 78
  • 209
  • 280
652
votes
15 answers

MySql export schema without data

I'm using a MySql database with a Java program, now I want to give the program to somebody else. How to export the MySql database structure without the data in it, just the structure?
Darth Blue Ray
  • 9,445
  • 10
  • 35
  • 48
1 2 3
…
99 100