Basic help on using HeidiSQL

Requirements

HeidiSQL runs fine on Windows 11 (and on Windows 7 + 8 + 10 with some minor issues). Running HeidiSQL on Wine is currently quite unstable. The native Linux version may be an alternative for you if you get issues through Wine.

The normal way to install HeidiSQL is to download a release installer from the downloads page. After installingyou may download nightly buildseither from the download page directlyor through HeidiSQL itself (About > Check for updates).

Connecting to a server

Basics

HeidiSQL is a so called client applicationonly usable when you have some server available. Somake sure you have some MariaDBMySQLMS SQLPostgreSQL server or SQLite database file to connect to.

A simple setup is to have a MariaDB server installed on localhost (equivalent to the special IP address 127.0.0.1). In HeidiSQL's session manageryou click on the "New" button to create a new connectionand most default settings are already set for youexcept from the passwordwhich is mostly not an empty one on a newly installed MariaDB server.

Prompt for credentials

Username and password will be prompted each time you connect to this session

Use Windows authentication

Username and password will be rooted from your current Windows session to the server connection. Only available on MySQLMariaDB and MS SQL.

Cipher

SQLite only: select one of the supported encryption algorithms throught the drop-down arrow on the right, for instance "rc4" (an alias for "System.Data.SQLite"). You need to provide the key for the main database in the "Key" field then.

Compressed client/server protocol

Compress traffic between HeidiSQL and the server. Recommended only on low network bandwidthor when result sets are large. Only available on MySQL and MariaDB

Database(s)

If emptyHeidiSQL shows all databases to which you have access. You can limit the database tree with this settingby entering only the database names you want to show.

Encryption parameters

SQLite only: Configure cipher encryption parametersin the form "param1=1;param2=200;...". See the supported ciphers page for supported parameter-value pairs.

Comment

Any text or note you like. The list of sessions on the left side can show this comment in a column.

You can organize your stored sessions in folders. To create a folderclick the dropdown arrow on the "New" buttonthen click "Folder in root folder" or "Folder in selected folder". Once you have a folderyou can create connections in itor drag existing connections into that folder.

Libraries

HeidiSQL needs a database-specific dynamic library for connecting to your server. For exampleaccessing a MySQL server requires you to have either libmysql.dll or libmariadb.dll installed. The Windows installer ships all required libraries which you may need.

The Linux version of HeidiSQL however does not ship these libraries. So they need to get installed by either the .deb package dependenciesor you install them by handfor instance like so:

sudo apt-get install libmysqlclient-dev
sudo apt-get install libmariadb-dev
sudo apt-get install libpq5
sudo apt-get install libsqlite3-dev

Setting up a SSH tunnel connection to MariaDB/MySQL/PostgreSQL

If your MariaDB/MySQL/PostgreSQL server is located on a remote machine which is only accessible via SSHthen you still can use HeidiSQL to connect to it. The HeidiSQL installer places plink.exe in the right folderso you just need to select it from the pulldown menu. In more recent versionsyou can use "ssh.exe" as an alternativethe OpenSSH implementation from Microsoft. In both casesyou need to tell HeidiSQL the SSH credentials plus the MariaDB/MySQL/PostgreSQL/MSSQL credentials.

Note that the default host name for the SSH server is the one you entered in the "Settings" tab. HeidiSQL then advices plink.exe to connect to that host nameorwhen you entered a SSH host namethat one is taken. Additionallythe host name on the "Settings" tab is always taken for the -L (listen) option in plink.exe.

Example settings:

• "Settings" tab:
  • Hostname: "127.0.0.1"
  • Password: [your mysql password]
  • Port: "3306" in most cases
• "SSH tunnel tab:
  • SSH Host: [your server name]
  • Port: "22" in most cases
  • Username: [your ssh user]
  • Password: [your ssh password]
  • Local port: "3307"

The following erroror a similar oneis mostly caused by a tunnel onto the official IP address of your remote server:

Lost connection to MySQL server at 'reading initial communication packet'system error: 0 "Internal error/check (Not system error)"

In such casesensure you're using "127.0.0.1" in Settings > Hostname/IPand the remote IP of your server in SSH tunnel > Host.

Command line switches

Although HeidiSQL is a pure GUI applicationit can be automated for connecting and opening files via command line parameters. Parameter names are case sensitive and are based on those used by the MariaDB/MySQL command line applicationse.g. mysqldump.

Common pitfalls:

• Be sure to call HeidiSQL with its full file name ("heidisql.exe")not with the short version ("heidisql"). HeidiSQL's command line parser expects that this way. This should be fixed in the future.
• Parameter keys can be separated with = or one space from their valuee.g. -h=localhost
• Parameters containing a dot must be wrapped in double quotes. This is important when passing an IP address: -h=192.168.1.1 will use only the first segment 192while -h="192.168.1.1" is the correct form.

Examples:

• Start over using stored settings from session "xyz":
  • c:\path\to\heidisql.exe -d=xyz
  • c:\path\to\heidisql.exe -description=xyz
• Connect with different username or port:
  • c:\path\to\heidisql.exe -d=xyz -u=OtherUser
  • c:\path\to\heidisql.exe -d=xyz -P=3307
• Connect to a non-stored session:
  • c:\path\to\heidisql.exe -h="127.0.0.1" -u=root -p=Mypass -P=3307
• Open multiple .sql files in query tabs:
  • c:\path\to\heidisql.exe fileA.sql path\to\fileB.sql fileC.sql ...
• Use custom portable settings file:
  • c:\path\to\heidisql.exe --psettings=c:\temp\p.txt

The database tree

When you have a large amount of tablesviews or whatever in your database(s)you probably want to group these by their typefor a better overview. Just right click the tree and activate Tree options > Group objects by type:

You can also mark important items as so called favoritesby mouse click on the very left area of a table. Afterwardsyou can limit the tree to show only favorites by a click on the new "Show only favorites" button at the top:

Creating a table

HeidiSQL comes with a feature-rich GUI for creating and editing a table structure. Just right click the datatabase in which you want to create a tablethen point on "Create new"then click "Table":

Done thatyou'll see the table editor like in the following picture:

Creating a view

Click "Create new"then click "View"to show up the view editor. Creating a view is basically like writing a SELECT query. Give it a nameand click the save button to create it. HeidiSQL shows the data of the view in the "Data" tablike for tables.

One thing you might notice is that MySQL and MariaDB are reformatting the SELECT query in the view when you save it. This destroys indentationand converts the whole query into a one-liner. HeidiSQL tries its best to restore the original code of the viewby loading it from the *.frm file on the server. However, this fails in many casesoften due to restricted file privileges. For such casesthe only way to make it readable again is to use HeidiSQL's reformatter (Ctrl+F8).

Creating a stored procedure

Just right click the datatabase in which you want to create a procedurethen point on "Create new"then click "Procedure" or "Function". Done thatyou'll see the procedure editor like in the following picture:

Creating a trigger

Creating a scheduled event

The Data tab

On the data tabthe contents of the currently selected table or view are displayed. This is one of the most useful and powerful features of HeidiSQL. You will see different colors for the various groups of data types. These colors are customizable in Tools > Preferences > Data appearance.

Pressing F2 or one-long-click in a grid cell will start the editor mode. This will allow you inserting ordinary values into a row. For inserting special valuessuch as SQL functionsNULL or GUIDsright click a celland point to the Insert value > submenu.

Quick filters: Right click a value in the gridthen click Quick filter to get various one-click options to create a WHERE clause on the grid values. This filter can be base on either the focused cell in the grida prompted valueor on the contents of your clipboard.

In the Quick filter sub menuyou will find a More values sub-sub-menu. Pointing to that menu, HeidiSQL quickly collects and displays the top 30 items in the focused columngrouped by their value:

Finding specific values in such a grid can be a pain. For a simple client side filteryou can enter some value in the filter panel. Activate it in Edit > Filter panel (Ctrl+Alt+F):

HeidiSQL can also assists you with a Search and replace dialog (Find mode: Ctrl+FReplace mode: Ctrl+R). That dialog can be used on SQL query tabs too.

Binary valuesalso called BLOBsare by default displayed in hexadecimal formatwhich is mostly unreadable for a human being. For cases in which such BLOBs contain readable textHeidiSQL offers to toggle between hex-mode and text-mode. Press or "unpress" the white button with the purple "0x" on it:

Probably you have a table with one or more integer columns which represent UNIX timestamps. HeidiSQL can display such integer columns as date/time valuesso you can better read them:

Running SQL queries

HeidiSQL has a "Query" tab by default. You can create more than this default one by pressing Ctrl+Tor by right clicking the main tabsthen click "New query tab". In such a query tabyou can write your own database queriesor load a .sql file from your harddisk. Pressing F9or the button with the blue "play" icon on it executes your query or queries.

Compound queries e.g. for creating a function often have a semicolon inside the query. As HeidiSQL separates queries at each semicolonyou will get syntax errors when you run such commands. You can set a different query delimiter for such purposese.g. "!!"as shown in the screenshot:

Alternativelyyou can change the delimiter via code:

DELIMITER !!
-- your code
DELIMITER ;

On the right hand of each query tabyou have the "query helpers" panelwith table columnsreserved words, SQL functions etc.
Having a table selected in the left treethe first tree item in the helpers show "Columns in <selected-table>". The contained "Generate..." menu items use the selected column names to create a quick query for you in the editor:

To see how your query performs in MariaDB or MySQLyou can activate the "Query profile" option in the helpers box on the right. Thenrun your query or queriesand see what the profile timings show. This is basically what SHOW PROFILE in MySQL 5.0.37 and later releases does.

HeidiSQL supports parameterized SQL queries: Activate it per click on the checkbox "Bind parameter"and start writing a query with parameterse.g. select ':p'.

The first part of the bind parameter implementation was done by Adrian Granger.

HeidiSQL can execute a batch of queries (= multiple queriesseparated by semicolon) in one go. That wayexecution gets dramatically fasterespecially when having tons of mini queries. To activate that "one go execution"just click the drop down menu of the blue "play" buttonthen click "Send batch in one go":

MS SQL users should turn this on when having trouble with declaring a variablee.g.:

Declare @RowNo int =1;
SELECT @RowNo;

SQL export

HeidiSQL can generate nice SQL export files for you. This is basically what mysqldump also does. Howeveryou can also tell HeidiSQL to put the results of the export into

• an .sql file
• a ZIP compressed .sql file
• into your clipboard
• another database on the same server
• a new or existing database on a server on which you have a configured HeidiSQL session

Database(s)

Controls whether to a) drop an existing database on the target server firstand b) create it.
This is meant for the target servernot the source server!

Table(s)

Same as abovejust for tablesviewsprocedures etc. When checkedthe table(s) will be dropped first, and/or created afterwards.

Data

How the rows are created on the target server. Select "No data" to make a structure-only export. Default is "INSERT".

Max INSERT size

Defines the maximum size of the extended INSERT statementswhich can hold multiple rows in one statement. Note that a MariaDB/MySQL server limits the size of a query sent to the server by the server variable max_allowed_packet. Be sure to use a lower value in this dialogotherwise MariaDB/MySQL will kill your connection when importing that file.

Output

Either select a filename (zip compressed or uncompressed)a directory in which HeidiSQL will place .sql filesclipboard, another database or a configured HeidiSQL (and a database name below).

FilenameDirectoryor Database

Depends on what you selected in the "output" pulldown menu.

Importing files

HeidiSQL can import .sql files with data rows and/or structure.csv files with data rows and binary files for BLOBs.

Importing .sql files

At firstactivate the database you want the import to run in. Thengo to File > "Run SQL file"and select the file to import. Below the filename input boxyou will find an Encoding dropdown menu. Using "Auto detect" is a common way to get broken data afterwards - so if are sure about the encoding in that fileyou should definitely select the right onee.g. "UTF-8". After clicking "OK"HeidiSQL will start to execute the contained commands immediatelyand you can watch the progress:

Importing .csv files (text files)

A .csv file is a text file with data rows for one table. You can import such a file into a table via Tools > "Import CSV file". You will have to tell HeidiSQL the format of the file (line terminatorenclosing character etc.).

Importing binary or text files into BLOBs

Via Tools > "Insert files into TEXT/BLOB fields"you can insert new rows from all kind of files into your tables. Firstselect the database and the table you want to put the files into. Thenclick on the green "Add" buttonto add one or more files to the listing. In the upper listing you will have to tell HeidiSQL with placeholders in which column the file content goes. In the "Value" columnclick besides the right field name and select '%filecontent%'. Some other placeholders are available in that dropdown. You can also wrap the placeholders (or even static text) with some SQL function. For example if you want the file name in lowercaseyou apply LOWER('%filename%').

HeidiSQL portable

If HeidiSQL finds a portable.lock fileor a portable_settings.txt, or the custom filename as noted aboveit starts in portable mode. Which means basically that all settings are restored from that file and when exiting stored again into that file.

When you download the portable packagethat portable_settings.txt needs to be manually copied from your old portable HeidiSQL directory. Not doing so will show you an empty session manager.

License

HeidiSQL is OpenSource and released under GPL (GNU GENERAL PUBLIC LICENSE). See the license.txt for more details.

Probably HeidiSQL saved you a lot of time and you like it. In this case you may make a donation here.

Credits

Current contributors:

• Ansgar Becker (Germany): Author
• Gustavo 'Gus' Carreno (United Kingdom): GitHub Actionsautomation
• Nico (Austria): ArchLinux packaging

Third party components and graphicsin a random order:

• Oleh Prypin (nightly.linkAGPL 3 license)
• Andrey (MetaDarkStyleGPL 2.1 license)
• Ulrich Telle (SQLite encryptionMIT license)
• Joachim Marder (VirtualTreeView, GLPL)
• Christian Budde (SynEditMPL)
• Serhiy Perevoznyk (JumpList)
• Iztok Kacin (Cromis.DirectoryWatchBSD License)
• Thomas Müller (GNU Gettext for DelphiBSD License)
• Rodrigo Ruz (VCL Styles Utils)
• Dennis D. Spreen (VerySimpleXML)
• Charlie Etienne (Material Theme)
• Mahdi Safsafi (Delphi Detours Library)
• Volker Siebert (SizeGripM.I.T. License)
• Jeremy Dorn (sql-formatterMIT License)
• Jordan Russell (Inno Setup)
• Mathias Rauen (madExcept)
• Icons8 (New high resolution iconsicons8.com)
• Mark James (Silk iconsCreative Commons 2.5)
• Sven Lorenz (graphics)

Former development contributors:

• David Dindorp (Denmark): threadingcomplex stuffthinking
• Adrien Granger (France): query parameters
• Francisco Ernesto Teixeira (Brazil): motivationideas

Thanks to Transifex.com for a free translation accountand all the registered translators.

1000 thanks for great database and development software:

• Embarcadero (and Borland) for Delphi since 1995.
• The Lazarus and FreePascal community for developing Lazarus/FPC since 2001.
• Michael 'Monty' Widenus and his fellows for founding MySQL in 1995 and MariaDB in 2009
• Microsoft for their feature-rich SQL Serverported from Sybase in 1989
• Michael Stonebraker and Larry Rowe for evolving PostgreSQL from Ingres in 1996
• Dwayne Richard Hipp for authoring SQLite in 2000. Fun facts about SQLite.