Using MySQL dynamically, without table

Question

Is it possible to use MySQL dynamically to achieve something like the following:

SELECT DISTINCT x FROM ('aaa', 'bbb', 'aaa', 'ccc') x;

where the part 'aaa', 'bbb', 'aaa', 'ccc' represents a comma separated list of 1000000 values (not table names, not column names) (sorry for confusion).

So all I can do is copy and paste this comma separated list.

I cannot wrap UNIONs around all values.

Expected output:

x
------
aaa
bbb
ccc

Why not use command line tools?

I like MySQL for its syntax on how to manipulate data, GROUPING, SORTING, ... MySQL is the best and easiest to handle datasets.

Again:

A list is defined as:

'aaaa',
'bbbbb',
'aabb',
'ffff',
'dfdff',
'aabb',
'ddsss'
[+ 1000 of those lines]

Now I want to copy and paste this list and do MySQL operations to it. Example:

SELECT DISTINCT [paste list here];

Or:

SELECT * FROM [paste list here] WHERE value LIKE 'aaa%'

Kermit · Answer 1 · 2013-12-17T17:27:46.923

Are you looking for something like this?

SELECT DISTINCT a.x 
FROM 
(SELECT 'aaa' AS x
UNION ALL
SELECT 'bbb'
UNION ALL
SELECT 'aaa'
UNION ALL
SELECT 'ccc') a

Would result in

|   X |
|-----|
| aaa |
| bbb |
| ccc |

See a demo

If you have many values, then you should load them into a temporary table and run a DISTINCT on the temp table.

CREATE TEMPORARY TABLE [IF NOT EXISTS] listOfValues (
    value varchar(10)
);

INSERT INTO listOfValues (value)
('aaa'),
('bbb'),
('aaa'),
('ccc');

SELECT DISTINCT value FROM listOfValues;

score 7 · Answer 2 · edited May 23 '17 at 12:28

7

Based on your updated question, you can create a temporary table, use LOAD DATA INFILE (there's a command line option as well) and lastly run a DISTINCT on the rows. There's no way to "copy & paste" it, unless you want to put it in a blob or long text and write a parser which goes beyond what MySQL was designed for.

edited May 23 '17 at 12:28

Community

1
1

answered Dec 17 '13 at 17:23

Kermit

33,827
13
85
121

score -1 · Answer 3 · edited May 23 '17 at 11:50

-1

SELECT 'aaa'
UNION
SELECT 'bbb'
UNION 
SELECT 'aaa'
UNION 
SELECT 'ccc'

Will work

Union will remove the duplicates, Union all keeps duplicates With wanting to work with so many values maybe look here

remove dups from many csv files

Unless theres some reason it actually needs to be solved in SQL

edited May 23 '17 at 11:50

Community

1
1

answered Dec 17 '13 at 16:47

exussum

18,275
8
32
65

@user1281385 OP has yet to provide a valid reason why this needs to be solved using MySQL. – Kermit Dec 17 '13 at 17:19
We have both given the if yes hows, You could use the CSV engine too – exussum Dec 17 '13 at 17:22

Using MySQL dynamically, without table

So all I can do is copy and paste this comma separated list.

I cannot wrap UNIONs around all values.

3 Answers3