Highest scored 'sql+mysql' questions

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']; ...

Community wiki

44 revs, 36 users 14%
Andrew G. Johnson

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?

Lion King

33.4k

asked Apr 18, 2011 at 17:15

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

Majid Fouladpour

29.8k

asked Oct 12, 2011 at 19:42

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.

homerun

20.3k

asked Jan 19, 2012 at 8:37

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

Dean Rather

32k

asked Nov 10, 2008 at 2:34

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

Vijay Dev

27.2k

asked Aug 21, 2009 at 17:04

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

Keshan

14.5k

asked Nov 17, 2010 at 14:08

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

JCCyC

16.4k

asked Jun 19, 2009 at 16:16

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

Your Common Sense

157k

asked Apr 5, 2010 at 6:22

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

warren

33.1k

asked Sep 1, 2009 at 8:56

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

9,811

asked Mar 4, 2009 at 20:14

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?

Spencer

21.9k

asked Jan 25, 2011 at 17:34

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

jul

36.9k

asked Mar 19, 2013 at 14:03

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

Fernando Barrocal

12.8k

asked Feb 3, 2009 at 20:29

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

Amandasaurus

59.5k

asked Jun 26, 2009 at 15:22

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

Nate

27k

asked Jul 4, 2012 at 1:53

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

Richard Knop

82.4k

asked Apr 21, 2011 at 7:56

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

14k

asked May 10, 2011 at 13:53

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?

jimgh

7,477

asked Nov 12, 2009 at 6:10

678 votes

12 answers

459k views

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

Community wiki

7 revs, 4 users 45%
Zac

670 votes

16 answers

553k views

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,615

asked May 30, 2011 at 11:06

669 votes

19 answers

1.1m views

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

Zack Peterson

56.6k

asked Sep 19, 2008 at 21:27

582 votes

9 answers

1.1m views

How to declare a variable in MySQL?

How to declare a variable in mysql, so that my second query can use it? I would like to write something like: SET start = 1; SET finish = 10; SELECT * FROM places WHERE place BETWEEN start AND ...

cdub

25.2k

asked Aug 1, 2012 at 7:47

554 votes

29 answers

582k views

MySQL select 10 random rows from 600K rows fast

How can I best write a query that selects 10 rows randomly from a total of 600k?

Francisc

79k

asked Dec 1, 2010 at 21:35

553 votes

6 answers

451k views

MySQL: @variable vs. variable. What's the difference?

In another question I posted someone told me that there is a difference between: @variable and: variable in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can ...

aarona

36.7k

asked Jun 17, 2009 at 23:04

552 votes

29 answers

2.0m views

MySQL Error: : 'Access denied for user 'root'@'localhost'

Consider: ./mysqladmin -u root -p** '_redacted_' Output (including typing the password): Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'...

Harsh Trivedi

5,761

asked Jan 14, 2017 at 0:06

534 votes

14 answers

552k views

Restrict results to top N rows per group

The following query: SELECT year, id, rate FROM h WHERE year BETWEEN 2000 AND 2009 ORDER BY id, rate DESC yields: year | id | rate 2006 | p01 | 8.0 2003 | p01 | 7.4 2008 | p01 | 6.8 2001 | ...

Wells

10.6k

asked Jan 25, 2010 at 1:25

513 votes

20 answers

606k views

MySQL error: key specification without a key length

I have a table with a primary key that is a varchar(255). Some cases have arisen where 255 characters isn't enough. I tried changing the field to a text, but I get the following error: BLOB/TEXT ...

GSto

41.9k

asked Dec 1, 2009 at 15:50

509 votes

18 answers

736k views

Error related to only_full_group_by when executing a query in MySql

I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySQL it works ...

Dan Bemowski

5,365

asked Dec 6, 2015 at 7:42

494 votes

34 answers

1.1m views

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native ...

Charles Roper

20.5k

asked Aug 19, 2008 at 17:13

486 votes

18 answers

690k views

Rename a column in MySQL

I am trying to rename a column in MySQL community server 5.5.27 using this SQL expression: ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name; I also tried ALTER TABLE table_name ...

Michael Okoli

5,068

asked May 17, 2015 at 18:59

477 votes

2 answers

539k views

Delete all Duplicate Rows except for One in MySQL? [duplicate]

How would I delete all duplicate data from a MySQL Table? For example, with the following data: SELECT * FROM names; +----+--------+ | id | name | +----+--------+ | 1 | google | | 2 | yahoo | |...

Highway of Life

23.6k

asked Jan 13, 2011 at 20:58

466 votes

6 answers

924k views

SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one ...

Community wiki

12 revs, 5 users 64%
Fluffeh

455 votes

6 answers

365k views

How to use GROUP BY to concatenate strings in MySQL?

Basically the question is how to get from this: foo_id foo_name 1 A 1 B 2 C to this: foo_id foo_name 1 A B 2 C

Paweł Hajdan

18.3k

asked Sep 29, 2008 at 17:32

453 votes

12 answers

359k views

You can't specify target table for update in FROM clause

I have a simple mysql table: CREATE TABLE IF NOT EXISTS `pers` ( `persID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(35) NOT NULL, `gehalt` int(11) NOT NULL, `chefID` int(11) DEFAULT ...

CSchulz

11k

asked Dec 13, 2010 at 13:36

449 votes

20 answers

501k views

Multiple Updates in MySQL

I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL? Edit: For example I have the following Name id Col1 Col2 Row1 1 ...

Teifion

110k

asked Aug 6, 2008 at 14:12

449 votes

17 answers

454k views

How to create a MySQL hierarchical recursive query?

I have a MySQL table which is as follows: id name parent_id 19 category1 0 20 category2 19 21 category3 20 22 category4 21 ... ... ... Now, I want to have a single MySQL query to which I simply ...

Tarun Parswani

4,735

asked Nov 26, 2013 at 11:22

445 votes

6 answers

565k views

Update a column value, replacing part of a string

I have a table with the following columns in a MySQL database [id, url] And the URLs are like: http://domain1.example/images/img1.jpg I want to update all the URLs to another domain http://domain2....

Addev

32.3k

asked Apr 16, 2012 at 15:36

445 votes

28 answers

473k views

Remove duplicate rows in MySQL

I have a table with the following fields: id (Unique) url (Unique) title company site_id Now, I need to remove rows having same title, company and site_id. One way to do it will be using the ...

Chetan

4,915

asked Jul 22, 2010 at 18:03

445 votes

24 answers

460k views

Get record counts for all tables in MySQL database

Is there a way to get the count of rows in all tables in a MySQL database without running a SELECT count() on each table?

Mark

4,829

asked Nov 13, 2008 at 1:41

445 votes

17 answers

773k views

What's the difference between VARCHAR and CHAR?

What's the difference between VARCHAR and CHAR in MySQL? I am trying to store MD5 hashes.

steven

13.4k

asked Dec 11, 2009 at 3:32

442 votes

29 answers

1.1m views

Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction

I'm running the following MySQL UPDATE statement: mysql> update customer set account_import_id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction I'm not using a ...

Jason Swett

44.4k

asked Apr 29, 2011 at 19:40

438 votes

37 answers

956k views

Error Code: 2013. Lost connection to MySQL server during query

I got the Error Code: 2013. Lost connection to MySQL server during query error when I tried to add an index to a table using MySQL Workbench. I also noticed that it appears whenever I run long query. ...

user836026

11k

asked May 12, 2012 at 12:14

436 votes

16 answers

560k views

How can I search (case-insensitive) in a column using LIKE wildcard?

I looked around some and didn't find what I was after so here goes. SELECT * FROM trees WHERE trees.`title` LIKE '%elm%' This works fine, but not if the tree is named Elm or ELM etc... How do I ...

David Morrow

9,174

asked May 20, 2010 at 18:39

426 votes

21 answers

1.2m views

Error 1046 No database selected, how to resolve?

Error SQL query: -- -- Database: `work` -- -- -------------------------------------------------------- -- -- Table structure for table `administrators` -- CREATE TABLE IF NOT EXISTS `administrators` ...

steph

4,277

asked Oct 23, 2010 at 18:19

426 votes

11 answers

336k views

Best database field type for a URL

I need to store a url in a MySQL table. What's the best practice for defining a field that will hold a URL with an undetermined length?

Jesse Hattabaugh

7,936

asked Oct 20, 2008 at 19:29

422 votes

19 answers

671k views

Get table column names in MySQL?

Is there a way to grab the columns name of a table in MySQL using PHP?

An employee

6,248

asked Oct 6, 2009 at 16:32

421 votes

12 answers

486k views

Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or is ...

Bernard Chen

6,477

asked Nov 4, 2009 at 20:55

416 votes

8 answers

1.2m views

MySQL query String contains

I've been trying to figure out how I can make a query with MySQL that checks if the value (string $haystack ) in a certain column contains certain data (string $needle), like this: SELECT * FROM `...

arik

28.8k

asked Apr 8, 2010 at 17:51

409 votes

12 answers

358k views

How can I make SQL case sensitive string comparison on MySQL?

I have a function that returns five characters with mixed case. If I do a query on this string it will return the value regardless of case. How can I make MySQL string queries case sensitive?

StevenB

4,125

asked Apr 12, 2011 at 0:36

Collectives™ on Stack Overflow

All Questions

Related Tags