MySQL Drop All Tables At a Glance - For Speed Up Development

There may come a time when you need to drop all of your tables in a MySQL database but don't want to delete database. Consider you have 100+ tables in your database. So it will be big problem for all of us to delete all tables in short time. If you have foreign keys in your database, then you may encounter errors if you drop a table that is related to another table using foreign keys. The quicker way to do this is to disable the foreign key checks when these statements are run, so you can drop the tables and avoid the error. Add this line above all of your Drop Table statements to disable the foreign key checks: SET FOREIGN_KEY_CHECKS = 0; Then, add this line at the end of your script to enable them: SET FOREIGN_KEY_CHECKS = 1;

So what we will do is Drop All Tables In One Script to speed up our whole process.

SET FOREIGN_KEY_CHECKS = 0; SET GROUP_CONCAT_MAX_LEN=32768; SET @tables = NULL; SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables FROM information_schema.tables WHERE table_schema = (SELECT DATABASE()); SELECT IFNULL(@tables,'dummy') INTO @tables; SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables); PREPARE stmt FROM @tables; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1;

MySQL Installation With Error: Microsoft Visual C++ 2019 Redistributable Package (x64) is not installed. Latest binary compatible version will be installed if agreed to resolve this requirement.

Microsoft Visual C++ 2019 Redistributable Package (x64) is not installed. Latest binary compatible version will be installed if agreed to resolve this requirement.

You need to download latest Microsoft Visual C++ Redistributable Package to resolve the problem.

You can download latest one from https://www.microsoft.com/en-us/download/details.aspx?id=48145

How to Install MySQL on Windows

Download the MySQL Installer from dev.mysql.com. The two download options are a web-community version and a full version. The web-community version will only download the server, by default, but you can select other applications (like Workbench) as desired. The full installer will download the server and all the recommended additional applications. (You’ll also be asked to create a user account, but you skip this part by scrolling down to the bottom and clicking "No thanks, just start my download".)

Run the installer that you downloaded

Determine which setup type you would like to use for the installation Developer Default: this is the full installation of MySQL Server and the other tools needed for development. If you are building your database from the ground up or will be managing the data directly in the database, you’ll want to use this setup type. Server Only: if you only need MySQL Server installed for use with a CMS or other application and will not be managing the database directly, you can install just the server (you can always install additional tools later). Custom: this setup type will allow you to customize every part of the installation from the server version to whichever additional tools you select.

Install the server instance and whichever additional products you selected. Then begin the configuration process by selecting the availability level (most users will use the default, standalone version). Complete the configuration process by following the on-screen instructions. You’ll want to make sure to install MySQL as a Service so that Windows can automatically start the service after a reboot or can restart the service if it fails. For additional, step-by-step instructions, see MySQL Server Configuration with MySQL Installer.

And finally MySQL installed into your system.

MySQL Count the number of occurrences / number of existance of a string in a text field

SET @search = "pri"; SELECT details, ROUND((LENGTH(details) - LENGTH( REPLACE ( details, LOWER(@search), ""))) / LENGTH(@search)) AS count FROM user_search_index ORDER BY COUNT desc

Grails on Groovy | From within a grails HQL, how would I use a MySQL / Oracle Native Function | HQL Query use MySQL Native Functions | Register Functions for MySQL Native Support

To call a MySQL native function in HQL query builder, the SQL dialect must be aware of it. You can add your function at runtime in BootStrap.groovy like this:

import org.codehaus.groovy.grails.commons.GrailsApplication import org.codehaus.groovy.grails.web.servlet.GrailsApplicationAttributes import org.hibernate.dialect.function.SQLFunctionTemplate import org.hibernate.type.StringType import org.springframework.context.ApplicationContext class BootStrap { def init = { servletContext -> ApplicationContext applicationContext = servletContext.getAttribute(GrailsApplicationAttributes.APPLICATION_CONTEXT) GrailsApplication application = (GrailsApplication) applicationContext.getBean("grailsApplication") def dialect = applicationContext.sessionFactory.dialect def MyFunction = new SQLFunctionTemplate(StringType.INSTANCE, "MyFunction(?1)") dialect.registerFunction('MyFunction', MyFunction) } def destroy = { } }

MySQL native function registered with our Grails project, now we are able to call the function in our HQL queries like below:

package com.pkm import grails.transaction.Transactional @Transactional class HomeService { void callMe() { List result = Table1.executeQuery("select id,name,roll,MyFunction(id) from Table1") println(result) } }

MySQL > Create Function > Call MySQL Function > Execution MySQL Function > MysQL Show Listed Functions

MySQL find_in_set Sith Multiple Search String | MySQL Variable With Multiple Values | MySQL Use of REGEXP | Search IN With Multiple Values

Problem is like below:

We know that find_in_set only search by a single string find_in_set('2', '2,4,6,8') In the above example, '2' is the only string used for search. So we are looking for if there is any way to use find_in_set kind of functionality and search by multiple strings, like find_in_set('2,4,6', '2,3,4,6,8,10')

The only solution is to use REGEXP

where CONCAT(",", t2.id, ",") REGEXP ",(2|4|6|8|10),"

Full query would be like below:

SELECT t1.*, SUM(t2.score) AS score_total, GROUP_CONCAT(t2.subject ORDER BY t2.id ASC) AS subjects, GROUP_CONCAT(t2.id ORDER BY t2.id ASC) AS t2_id FROM table1 t1 JOIN table2_child t2 ON (t1.id = t2.table1_id) WHERE CONCAT(",", t2.id, ",") REGEXP ",(2|4|6|8|10)," GROUP BY t1.id

MySQL > Create Function > Call MySQL Function > Execution MySQL Function > MysQL Show Listed Functions

Use of MySQL function will make our coding easier. Function can contain some business logic.

So I will create MySQL function first using below query:

DELIMITER // DROP FUNCTION IF EXISTS MyFunction; CREATE FUNCTION MyFunction(p1 INT) RETURNS VARCHAR(50) BEGIN DECLARE v1 VARCHAR(50) DEFAULT ""; SELECT concat(name,'-',roll) INTO v1 FROM table1 WHERE id = p1; RETURN v1; END; //

So our MySQL function created, now we will list our available MySQL functions using below query:

SHOW FUNCTION STATUS

Now we will select/execute MySQL function using below query

select *,MyFunction(id) as 'function' from table1;

Which will results like below screenshot:

Extending from Laravel 5 core - SqlServerConnection | Extending The Connection Class In Laravel

The first thing the ConnectionFactory::createConnection() method does is to check if the db.connection.{$driver} alias is bound, and if so, it returns that connection object. If it is not bound, it returns the base connection object (Illuminate\Database\MySqlServerConnection for the mysql driver)

Therefore, all you need to do to use your own custom connection is to bind the db.connection.mysql alias to your custom MySqlServerConnection class

You can create a new service provider in which to do this, or you can just add the line to your existing AppServiceProvider

<?php namespace App\Providers; use Illuminate\Contracts\Events\Dispatcher as DispatcherContract; use Illuminate\Foundation\Support\Providers\EventServiceProvider as ServiceProvider; class AppServiceProvider extends ServiceProvider { public function boot(DispatcherContract $events) { parent::boot($events); } public function register() { $this->app->bind('db.connection.mysql', \App\Database\MySqlConnection::class); } }

Remember that there may be two methods in any ServiceProvider, first call "register" method of each ServiceProvider listed and after that "boot" method called each ServiceProvider listed in "config/app.php" in providers section.

<?php return [ 'providers' => [ App\Providers\EventServiceProvider::class, App\Providers\AppServiceProvider::class ] ];

You have to create a MySQL connection class \App\Database\MySqlConnection

<?php namespace App\Database; use Illuminate\Database\MySqlConnection as ParentMySqlConnection; class MySqlConnection extends ParentMySqlConnection { public function select($query, $bindings = [], $useReadPdo = true) { return parent::select($query, $bindings, $useReadPdo); } }

So its completed, MySqlConnection is now extended with our own connection class.

So you can now anything do with your custom connection class.

Get Latest Record In Each MySQL Group | How to select the first/least/max row per group in SQL | Select max, min, last row for each group in SQL without a subquery | MySQL - How To Get Top N Rows per Each Group

First approach (LEFT JOIN)

---

SELECT s.Name,c1.Id AS Max_Score_ID,c1.Score as Max_Score
FROM Students s
LEFT JOIN Scores c1 ON (c1.Student=s.Id)
LEFT JOIN Scores c2 ON (
  c1.Student=c2.Student 
  AND (c1.Score<c2.Score OR (c1.Score=c2.Score AND c1.Id<c2.Id))
)
WHERE c2.Score IS NULL
ORDER BY c1.Score DESC;

Execution Plan: First image shows statistics, an index is used when performing the above SQL, example created in MySQL-Fiddle. And second image for large data size, its around 8 million rows, and it takes a small amount of time to execute.

---

Now we will go for second approach (GROUP_CONCAT WITH SUBSTRING_INDEX)

---

SELECT s.Name,
CAST(SUBSTRING_INDEX(
  GROUP_CONCAT(c1.Id order by c1.Score desc),',',1
) AS DECIMAL(10, 2)) AS Max_Score_ID,
CAST(SUBSTRING_INDEX(
  GROUP_CONCAT(c1.Score order by c1.Score desc),',',1
) AS DECIMAL(10, 2)) as Max_Score
FROM Students s
LEFT JOIN Scores c1 ON (c1.Student=s.Id)
GROUP BY s.Id
ORDER BY Max_Score DESC;

Execution plan: First image show statistics taken from MySQL-Fiddle, used group-concat and substring-index both to calculate (min/max) value and/or other column of the table. Second image show that its also good time effective as it also takes a small amount of time to get min/max value and table size around 8 million rows.

---

MySQL Fiddle Link

Get Latest Record In Each MySQL Group

Table=check_group_by
id   group_by    value
-------------------------
1    1           Value 1
2    1           Value 2
3    1           Value 3
4    2           Value 4
5    2           Value 5
6    3           Value 6

Query will be as like:

SELECT id,group_by,value FROM check_group_by
WHERE id IN (
 SELECT MAX(id) FROM check_group_by GROUP BY group_by
)

SQL Fiddle Example

MYSQL Group Concat Select Some Selected Rows Only | Use Sort In Group Concat | Sort MySQL Rows in Group Concat

Its so simple. Just need to do below thins:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(x.id ORDER BY x.nx DESC), ',', 2) as row_name from some_table GROUP BY some_field

It will select First two values only.

It total value of GROUP_CONCAT is "1,2,3,4,5" Then Using SUBSTRING_INDEX would be like "1,2"

You can use DISTINCT in GROUP_CONCAT function like SUBSTRING_INDEX(DISTINCT(GROUP_CONCAT(x.id ORDER BY x.nx DESC)), ',', 2)

Mysql GROUP_CONCAT of first n rows || Selecting first and last values in a group || Select first and last row from group_concat when grouping sortable days

select s.id,count(e.id) as total,
substring_index(group_concat(e.id order by e.id asc),',',5) as group_value
from students s left join exams e on s.id=e.student_id
group by s.id
order by count(e.id) desc

MySQL Table Create Index Remove Index List Index | Primary Indexing | Unique Indexing | Field Indexing | FullText Indexing

/* Primary INDEXING */
ALTER TABLE `TABLE_NAME` ADD PRIMARY KEY (`ID`);

/* General INDEXING (1 or more field) */
ALTER TABLE `TABLE_NAME` ADD INDEX `INDEX_NAME` (`FIELD_1`, `FIELD_2`);

/* Unique INDEXING (1 or more field) */
ALTER TABLE `TABLE_NAME` ADD UNIQUE `INDEX_NAME` (`FIELD_1`, `FIELD_2`);

/* Fulltext INDEXING (1 or more field) */
ALTER TABLE `TABLE_NAME` ADD FULLTEXT `INDEX_NAME` (`FIELD_1`, `FIELD_2`);

/* Remove INDEXING */
DROP INDEX `INDEX_NAME` ON `TABLE_NAME`;

/* List INDEXING */
SHOW INDEX FROM `TABLE_NAME`;

MYSQL : SQL For Create Table Example

CREATE TABLE `table_name` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `int_field` BIGINT(20) NOT NULL DEFAULT '0',
  `string_field` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
  `long_text_field` LONGTEXT NULL COLLATE 'utf8_unicode_ci',
  `time_stamp_field` TIMESTAMP NULL DEFAULT NULL,
  `date_field` DATE NULL DEFAULT NULL,
  `date_time_field` DATETIME NULL DEFAULT NULL,
  `tinyint_field` TINYINT(1) NOT NULL DEFAULT '0',
  `double_field` DOUBLE(30,10) NULL DEFAULT '0.0000000000',
  `enum_field` ENUM('TRUE','FALSE') NULL DEFAULT 'TRUE' COLLATE 'utf8_unicode_ci',
  `set_field` SET('XA','XB','XC') NULL DEFAULT 'XA,XB' COLLATE 'utf8_unicode_ci',
  `uuid` VARCHAR(70) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uuid` (`uuid`),
  INDEX `int_field_index` (`int_field`),
  INDEX `int_field_index_complex` (`int_field`, `string_field`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

MYSQL Backup Database Using Command Line | MYSQL Import / Restore Database Using Command Line

To take backup database, you need to execute following command:

mysqldump -u root -p password db_name > /tmp/sql_file.sql


And you can import/restore database using following command:

mysql -u root -p password db_name < /tmp/sql_file.sql

How to encode BASE64 via MySQL | MySQL from BASE64 | BASE64 ENCODE AND DECODE IN MySQL | BASE64 encode in MySQL

How to encode BASE64 via MySQL | MySQL from BASE64 | BASE64 ENCODE AND DECODE IN MySQL | BASE64 encode in MySQL

I want to select a blob col from one table, BASE64 encode it and insert it into another tables. Is there any way to do this without round tripping the data out of the DB and through my app?

I was looking for the same thing and I've just seen that MySQL 5.6 has a couple of new string functions supporting this functionality: TO_BASE64 and FROM_BASE64.

SELECT FROM_BASE64('YmFzZTY0IGVuY29kZWQgc3RyaW5n');


SELECT TO_BASE64(field_name) FROM table_name;

How can I set a default sort for tables in PHPMyAdmin (i.e. always “Primary key - Descending”) | MySQL - Automatic ordering by ID not working | MySQL sort by a column by default in phpMyAdmin

How can I set a default sort for tables in PHPMyAdmin (i.e. always “Primary key - Descending”) | MySQL - Automatic ordering by ID not working | MySQL sort by a column by default in PHPMyAdmin.

Even though its obnoxious in a lot of ways I use PHPMyAdmin all the time to debug database issues while writing PHP. By default it sorts tables by primary key ascending. 99% of the time I would rather have the newest data (my test data) shown at the top by default rather than the useless first few records ever saved.


Is there a way to configure PHPMyAdmin to show the newest records by default? To alter similar behavior?

In PHPMyAdmin 4.5.0, maybe in earlier versions too, you can set the $cfg['TablePrimaryKeyOrder'] config like so:

$cfg['TablePrimaryKeyOrder'] = 'DESC';

This defines the default sort order for the tables, having a primary key, when there is no sort order defines externally. Acceptable values : [‘NONE’, ‘ASC’, ‘DESC’]

This sets the default sort if the table has a primary key and that no other sort has been applied to it