Import Large Amounts of Data to MySQL Quickly

Importing large amounts of data with SQL `INSERT` statements can take a long time.

It's much faster to use MySQL's `LOAD DATA` statement. https://dev.mysql.com/doc/refman/5.7/en/load-data.html

The data you upload must be in some CSV format; you can choose delimiters and escape characters.

`LOAD DATA INFILE [filename] ...` will look for the file on the server where MySQL is running. Your MySQL user will need file permissions. MySQL is only allowed to read files on the server if they are in a certain directory.

`LOAD DATA LOCAL INFILE [filename] ...` will look for the file on the machine that has connected to the MySQL instance. There is no restriction on where the file must be stored so long as your OS user has permission to read it.

`LOAD DATA LOCAL` will only execute if you connected to MySQL with the `--secure-file-priv` option.

i.e. `mysql -h [hostname] -u user -p db --secure-file-priv`

DBMSes other than MySQL hopefully have a similar feature.

Comments

Post a Comment

Popular posts from this blog

Fix Controller Input when a Game Doesn't Use Steam Input

Background Knowledge Steam Input is an interface for developers to use the controller configuration built into Steam for their games. It is entirely optional and not all games use it. Steam gives you the option to enable and disable Steam Input on a per game basis. Steam has controller button mapping for in-game, Big Picture Mode, and on desktop (outside of games), and mappings for individual games. By default Steam will give certain controllers a desktop mapping that lets you move the mouse and bring up an on-screen keyboard, which is not what you want in a game. If you press F11 in a game and Steam Overlay doesn't appear and you have it configured to appear, Steam thinks that you are not in a game, and therefore it will use the desktop mapping instead of the in-game mapping. Steam Input works by capturing inputs from your controller, suppressing them from the rest of your system and emitting the inputs that you desire instead to the rest of your system. Problem If you are playin
Read more

Fix Inability to Move Files to Trash in VSC

You need to configure an environment variable to tell Electron (what VSC is built on) how to trash things. In ~/.profile add: export ELECTRON_TRASH=trash-put You'll need to install trash-put separately. "gio trash" might be a pre-installed alternative. I thought that's what I was using but apparently not.
Read more

VSC Vanishing Go Imports

With the Go extension installed in VSC, by default if you have an unused import, when you save, it will disappear. If you're like me and you may habitually press ctrl+s, this is very frustrating. After a lot of messing around in VSC's settings. I learned that this "feature" has nothing to do with the settings pertaining to linting or formatting. It's actually to do with automatic import organisation, which you can configure as follows in VSC's settings.json. "[go]" : { "editor.codeActionsOnSave" : { "source.organizeImports" : false, }, } This solution comes from this StackOverflow answer. You can still organise imports with the shortcut shift+alt+o.
Read more