New answers tagged

0

Switch to mysqldump; it is aimed at scripting, whereas Workbench is aimed at UI. Use the option --routines in addition to whatever else you need. --no-data gives you just the schema.


0

Try using following query DECLARE @MinDate DATE = '20160401', @MaxDate DATE = '20160430'; SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1) Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate) FROM sys.all_objects a CROSS JOIN sys.all_objects b; Please refer following link for more details ...


0

The comparison values can be held in a separate table and joined to the data table. This assumes the comparision clauses do not overlap, as you sample shows. In other words, each data row will match at most one filter condition. This is the data: create table data (c1 char(10)); insert data(c1) values ('aa1'),('aa2'),('bb1'),('bb2'),('cc1'),('cc2'); An ...


4

This case is a good candidate for using REGEXP instead of LIKE. SELECT * from table WHERE column REGEXP '^(AAA|BBB|CCC)' REGEXP is case insensitive. For case sensitive searches use REGEXP BINARY. SELECT * from table WHERE column REGEXP BINARY '^(AAA|BBB|CCC)' REGEXP can't benefit from indexes, so it may only be an efficient option if LIKE wouldn't ...


1

Here is a method using Dynamic SQL SET group_concat_max_len = 1048576; SELECT GROUP_CONCAT(CONCAT('SELECT * FROM table WHERE column LIKE ''',prfx,'%''') SEPARATOR ' UNION ') INTO @sql FROM (SELECT 'AAA' prfx UNION SELECT 'BBB' UNION SELECT 'CCC') pfxkeys; PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; So that you understand what this is doing, ...


0

SELECT a.foo, b.bar FROM db1.tableA AS a JOIN db2.tableB AS b ON a.something = b.whatever; This assumes both "databases" are on the same instance of MySQL on the same server. Otherwise, you need to reword the question.


0

The final error message shows the real problem. Your credentials are not correct or the user is not allow to connect. Hence MySQL Workbench asks again for them when you test the connection (as it failed to connect using your stored credentials).


0

"RPS" -- Requests/Sec? Is that INSERTs? SELECTs? Some combination? If it is mostly writes, then turn off the Query cache -- query_cache_type=0 and query_cache_size=0. This will eliminate the need to purge the QC. 1% RAM? If you are using MyISAM, then increase key_buffer_size to 20% of available RAM. If you are using InnoDB, then set ...



Top 50 recent answers are included