Secara umum, isi file disimpan di bawah tipe data Clob (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) di database MySQL
JDBC provides support for the Clob datatype, to store the contents of a file in to a table in a database
The setCharacterStream() method of the PreparedStatement interface accepts an integer representing the index of the parameter and, a Reader object as a parameter
Dan atur konten dari objek (file) pembaca yang diberikan sebagai nilai ke parameter (tempat penampung) dalam indeks yang ditentukan
Setiap kali Anda perlu mengirim nilai teks yang sangat besar, Anda dapat menggunakan metode ini
Menyimpan file teks menggunakan JDBC
Jika Anda perlu menyimpan file dalam database menggunakan program JDBC, buat tabel dengan tipe data Clob (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) seperti yang ditunjukkan di bawah ini
CREATE TABLE Articles(Name VARCHAR(255), Article LONGTEXT);Now, using JDBC connect to the database and prepare a PreparedStatement to insert values into the above created table
String query = "INSERT INTO Tutorial(Name, Article) VALUES (?,?)";PreparedStatement pstmt = con.prepareStatement(query);Set values to the place holders using the setter methods of the PreparedStatement interface and for the Clob datatype set value using the setCharacterStream() method
Example
Following is an example demonstrating how to insert file in to a MySQL database using JDBC program. Here we are have created a table with a Clob datatype, inserted values in to it
import java.io.FileReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class InsertingFileToDatabase { public static void main(String args[]) throws Exception { //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/sampleDB"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established......"); //Inserting values String query = "INSERT INTO Articles(Name, Article) VALUES (?, ?)"; PreparedStatement pstmt = con.prepareStatement(query); pstmt.setString(1, "article1"); FileReader reader = new FileReader("E:\data\article1.txt"); pstmt.setCharacterStream(2, reader); pstmt.execute(); pstmt.setString(1, "article2"); reader = new FileReader("E:\data\article2.txt"); pstmt.setCharacterStream(2, reader); pstmt.execute(); pstmt.setString(1, "article3"); reader = new FileReader("E:\data\article3.txt"); pstmt.setCharacterStream(2, reader); pstmt.execute(); System.out.println("Data inserted......"); } }Output
Connection established...... Data inserted......Using MySQL workbench, you can export the contents of a table to various files such as html files, . csv files text files etc. If you export the contents of the table after inserting data to a HTTML file its output will look like
Besides being able to store native JSON in MySQL, you can also use MySQL as a document store (doc store) to store JSON documents. And, you can use (create, read, update and delete) commands in either JavaScript or Python
This post will show you some of the basic commands for using MySQL as a document store
RequirementsTo use MySQL as a document store, you will need to use the following server features
The X Plugin enables the MySQL Server to communicate with clients using the X Protocol, which is a prerequisite for using MySQL as a document store. The X Plugin is enabled by default in MySQL Server as of MySQL 8. 0. For instructions to verify X Plugin installation and to configure and monitor X Plugin, see Section 20. 5, “X Plugin”
The X Protocol supports both CRUD and SQL operations, authentication via SASL, allows streaming (pipelining) of commands and is extensible on the protocol and the message layer. Clients compatible with X Protocol include MySQL Shell and MySQL 8. 0 Connectors
Clients that communicate with a MySQL Server using X Protocol can use X DevAPI to develop applications. X DevAPI offers a modern programming interface with a simple yet powerful design which provides support for established industry standard concepts. This chapter explains how to get started using either the JavaScript or Python implementation of X DevAPI in MySQL Shell as a client. See X DevAPI for in-depth tutorials on using X DevAPI
(source. https. //dev. mysql. com/doc/refman/8. 0/en/document-store. html)
And, you will need to have MySQL version 8. 0. x (or higher) installed, as well as the MySQL Shell version 8. 0. x (or higher). For these examples, I am using version 8. 0. 17 of both. (You could use version 5. 7. x, but I have not tested any of these commands in 5. 7. x)
Starting MySQL Shell (mysqlsh)When starting MySQL Shell (Shell), you have two session options. The default option is , and this allows the session to connect using the X Protocol. The other option when starting Shell is , which establishes a “Classic Session” and connects using the standard MySQL protocol. For this post, I am using the default option of . There are other MySQL Shell command-line options for the X Protocol available. And, there are specific X Protocol variables which may need to be set for the connection
Here is a list of all of the MySQL Shell commands and their shortcuts (for MySQL version 8. 0)
(source. https. //dev. mysql. com/doc/mysql-shell/8. 0/en/mysql-shell-commands. html)
Untuk memulai MySQL Shell (Shell), Anda cukup menjalankan perintah mysqlsh dari jendela terminal. The default mode is JavaScript (as shown by the JS in the prompt)
$ mysqlsh
MySQL Shell 8. 0. 17-commercial
Copyright (c) 2016, 2019, Oracle and/or its affiliates. Seluruh hak cipta
Oracle is a registered trademark of Oracle Corporation and/or its affiliates
Nama lain mungkin merupakan merek dagang dari pemiliknya masing-masing
Type '\help' or '\?' for help; '\quit' to exit
MySQL JS
By starting Shell without any variables, you will need to connect to a database instance. You do this with the \connect command, or you can use the \c shortcut. Sintaksnya adalah \c user@ip_address
SQLsaya JS \c root@127. 0. 0. 1
Membuat sesi ke 'root@127. 0. 0. 1'
Mengambil nama skema untuk pelengkapan otomatis. . . Tekan ^C untuk berhenti
Id koneksi MySQL Anda adalah 16 (protokol X)
Versi pelayan. 8. 0. 17-komersial MySQL Enterprise Server – Komersial
No default schema selected; type \use to set one
MySQL 127. 0. 0. 1. 33060+ ssl JS
Or, to start Shell with the connection information, specify the user and host IP address. The syntax is mysqlsh user@ip_address
$ mysqlsh root@127. 0. 0. 1
MySQL Shell 8. 0. 17-commercial
Copyright (c) 2016, 2019, Oracle and/or its affiliates. Seluruh hak cipta
Oracle is a registered trademark of Oracle Corporation and/or its affiliates
Nama lain mungkin merupakan merek dagang dari pemiliknya masing-masing
Type '\help' or '\?' for help; '\quit' to exit
Membuat sesi ke 'root@127. 0. 0. 1'
Mengambil nama skema untuk pelengkapan otomatis. . . Tekan ^C untuk berhenti
Your MySQL connection id is 18 (X protocol)
Versi pelayan. 8. 0. 17-komersial MySQL Enterprise Server – Komersial
No default schema selected; type \use to set one
MySQL 127. 0. 0. 1. 33060+ ssl JS
You may find a list of all of the command-line options at https. //dev. mysql. com/doc/mysql-shell/8. 0/en/mysqlsh. html
The Shell prompt displays the connection information, whether or not you are using ssl, and your current mode (there are three modes – JavaScript, Python and SQL). In the earlier example, you are in the (default) JavaScript mode. You can also get your session information with the session command
MySQL 127. 0. 0. 1. 33060+ ssl JS session
All of these commands are case-sensitive, so if you type an incorrect command, you will see the following error
MySQL 127. 0. 0. 1. 33060+ ssl JS Session
ReferenceError. Session is not defined
Here is how you switch between the three modes. – JavaScript, Python and SQL
MySQL 127. 0. 0. 1. 33060+ ssl JS \sql
Switching to SQL mode. . . Commands end with ;
MySQL 127. 0. 0. 1. 33060+ ssl SQL \py
Switching to Python mode. . .
SayaSQL 127. 0. 0. 1. 33060+ ssl Py \js
Beralih ke mode JavaScript. . .
MySQL 127. 0. 0. 1. 33060+ ssl JS
Ada juga beberapa format keluaran yang berbeda. Anda dapat mengubah output menggunakan shell. pilihan. mengatur perintah. Standarnya adalah tabel. Berikut adalah contoh masing-masing, menggunakan perintah yang sama
format keluaran tabel
SayaSQL 127. 0. 0. 1. 33060+ ssl JS cangkang. pilihan. set('resultFormat','tabel')
SayaSQL 127. 0. 0. 1. 33060+ ssl JS sesi. runSql('pilih pengguna, host dari mysql. pengguna')
+-‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
pengguna. tuan rumah
+-‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
mysql. skema info. localhost
mysql. session . localhost
mysql. sys. localhost
akar. localhost
+-‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
4 baris dalam set (0. 0005 detik)
format keluaran JSON
SayaSQL 127. 0. 0. 1. 33060+ ssl JS cangkang. pilihan. set('resultFormat','json')
SayaSQL 127. 0. 0. 1. 33060+ ssl JS sesi. runSql('pilih pengguna, host dari mysql. batas pengguna 2')
{
"pengguna". “mysql. infoskema”,
"tuan rumah". “localhost”
}
{
"pengguna". “mysql. sidang",
"tuan rumah". “localhost”
}
2 baris dalam set (0. 0005 detik)
berformat tab
SayaSQL 127. 0. 0. 1. 33060+ ssl JS shell. pilihan. set('resultFormat','tabbed')
SayaSQL 127. 0. 0. 1. 33060+ ssl sesi JS . runSql('pilih pengguna, host dari mysql. pengguna')
pengguna host
mysql. skema info localhost
mysql. sesi localhost
mysql. sys localhost
root localhost
4 baris dalam set (0. 0004 dtk)
format vertikal
SayaSQL 127. 0. 0. 1. 33060+ ssl JS shell. pilihan. set('resultFormat','vertikal')
SayaSQL 127. 0. 0. 1. 33060+ ssl sesi JS . runSql('pilih pengguna, host dari mysql. pesanan pengguna menurut pengguna desc')
************************** 1. baris ***************************
pengguna. mysql. skema info
tuan rumah. localhost
************************** 2. baris ***************************
pengguna. mysql. sidang
tuan rumah. localhost
************************** 3. row ***************************
pengguna. mysql. sys
tuan rumah. localhost
************************** 4. baris ***************************
pengguna. akar
tuan rumah. localhost
4 baris dalam set (0. 0005 detik)
Catatan. Dengan MySQL Doc Store, istilah untuk mendeskripsikan database, tabel, dan baris berbeda. Basis data disebut skema (walaupun penyimpanan dokumen "tanpa skema"). Tabel disebut koleksi, dan baris data disebut dokumen
Untuk membuat skema bernama test1, gunakan perintah createSchema
SayaSQL 127. 0. 0. 1. 33060+ ssl JS sesi. createSchema("test1")
Untuk mendapatkan daftar skema saat ini, gunakan perintah getSchemas
SayaSQL 127. 0. 0. 1. 33060+ ssl JS sesi. getSchemas()
[
,
,
,
,
]
Selain itu, Anda dapat menjalankan perintah SQL di dalam Doc Store – sesuatu yang tidak dapat Anda lakukan dengan sebagian besar database NoSQL lainnya. Alih-alih menggunakan perintah getSchemas, Anda dapat mengeluarkan perintah SHOW DATABASES SQL menggunakan perintah runSql NoSQL
SayaSQL 127. 0. 0. 1. 33060+ ssl JS sesi. runSql('tampilkan database')
+ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐an
Basis data
+ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐an
information_schema
mysql
skema_kinerja
sys
tes1
+ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐an
5 baris dalam set (0. 0052 dtk)
Untuk menjatuhkan skema bernama test1, Anda menggunakan perintah dropSchema
SayaSQL 127. 0. 0. 1. 33060+ ssl JS sesi. dropSchema("tes1")
Sama seperti dengan MySQL, Anda harus memilih skema (database) yang ingin Anda gunakan. Ini menunjukkan cara membuat skema dan mengaturnya sebagai skema default Anda. Dengan perintah \use, Anda benar-benar menyetel variabel bernama db agar sama dengan skema default. Jadi, setelah Anda mengatur skema Anda dengan perintah \use, saat Anda mengeluarkan perintah db, Anda dapat melihat skema default
SayaSQL 127. 0. 0. 1. 33060+ ssl JS sesi. createSchema("bengkel")
SayaSQL 127. 0. 0. 1. 33060+ ssl JS \use workshop
Skema default `workshop` dapat diakses melalui db
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db
Untuk mengubah nilai variabel db, Anda dapat menggunakan perintah \use atau Anda dapat mengatur nilai db menggunakan perintah var (variabel). Sesi komando. getSchema akan mengembalikan nilai skema, tetapi tidak secara otomatis menetapkan nilai variabel db. )
SayaSQL 127. 0. 0. 1. 33060+ ssl JS \use workshop
Skema default `workshop` dapat diakses melalui db
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db
SayaSQL 127. 0. 0. 1. 33060+ ssl lokakarya sesi JS . getSchema('mysql');
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS var db = sesi. getSchema('mysql');
SayaSQL 127. 0. 0. 1. 33060+ ssl mysql JS db
Anda juga dapat membuat variabel Anda sendiri. Berikut adalah contoh pengaturan variabel sdb sama dengan perintah SQL SHOW DATABASES
SayaSQL 127. 0. 0. 1. 33060+ ssl workshop JS var sdb = sesi. runSql('tampilkan database')
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS sdb
+ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐an
Basis data
+ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐an
information_schema
mysql
skema_kinerja
sys
bengkel
+ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐an
6 baris dalam set (0. 0079 dtk)
Tetapi variabelnya hanya bagus untuk sesi Anda saat ini. Jika Anda keluar dari Shell, masuk kembali, dan menjalankan kembali variabel, Anda akan mendapatkan pesan kesalahan yang menyatakan bahwa variabel tidak ditentukan
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS \q
Selamat tinggal
# akar mysqlsh@127. 0. 0. 1
. .
SayaSQL 127. 0. 0. 1. 33060+ ssl JS sdb
ReferensiKesalahan. sdb tidak didefinisikan
Setelah Anda membuat skema (database), dan memilihnya melalui perintah \use, maka Anda dapat membuat koleksi (tabel) dan memasukkan dokumen JSON ke dalam koleksi. Saya akan membuat koleksi bernama test1
SayaSQL 127. 0. 0. 1. 33060+ ssl JS \use workshop
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. createCollection("test1")
Anda bisa mendapatkan daftar koleksi dengan menggunakan perintah getCollections, dan Anda juga bisa menjalankan perintah SQL dengan session. perintah runSql. Perintah getCollections sama dengan perintah SQL SHOW TABLES
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. getCollections()
[
]
SayaSQL 127. 0. 0. 1. 33060+ ssl workshop sesi JS . runSql('TAMPILKAN TABEL')
+ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐an
Tabel_dalam_bengkel
+ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐an
tes1
+ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐an
1 baris dalam set (0. 0001 detik)
Untuk menjatuhkan koleksi, gunakan perintah dropCollection. Anda dapat memverifikasi bahwa koleksi telah dihapus dengan menggunakan perintah getCollections lagi
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. dropCollection("test1")
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. getCollections()
[]
Jika Anda belum memilih database default, Anda juga dapat menentukan nama skema sebelum nama koleksi (yang merupakan tipe sintaks yang sama saat Anda membuat tabel di dalam database di MySQL). Anda akan melihat hasilnya berbeda, seperti ketika Anda menentukan nama skema sebelum nama koleksi, nama skema itu juga dikembalikan
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. createCollection("makanan")
SayaSQL 127. 0. 0. 1. 33060+ ssl JS db. createCollection("bengkel. makanan2")
To add a JSON document to a collection, you use the add command. Anda harus menentukan koleksi sebelum menggunakan perintah add. (Anda tidak dapat mengeluarkan perintah seperti bengkel. makanan. menambahkan. ) Anda dapat menambahkan dokumen dengan atau tanpa pengembalian dan spasi ekstra (atau tab). Di sini dokumen JSON untuk ditambahkan ke koleksi bernama bengkel
{
Nama dulu. "Rubah",
Nama_Belakang. "Mulder",
makanan favorit. {
Sarapan. "Telur dan daging babi",
Makan siang. "sandwich babi tarik",
Makan malam. "steak dan kentang panggang"
}
}
Catatan. Beberapa format JSON memerlukan tanda kutip ganda di sekitar kunci/string. Dalam contoh ini, Name_First memiliki tanda kutip ganda pada contoh pertama, dan contoh kedua tidak memiliki tanda kutip ganda. Kedua format akan berfungsi di Shell
-> pada contoh di bawah ini ditambahkan oleh Shell. Anda tidak perlu mengetik ini di baris perintah
SayaSQL 127. 0. 0. 1. 33060+ ssl proyek JS db. makanan. tambahkan({
-> "Nama_Depan". "Steve"})
->
Kueri Oke, 1 item terpengaruh (0. 0141 detik)
SayaSQL 127. 0. 0. 1. 33060+ ssl proyek JS db. makanan. tambahkan({
-> Nama_Depan. "Steve"})
->
Kueri Oke, 1 item terpengaruh (0. 0048 dtk)
Berikut adalah contoh dari setiap metode untuk menambahkan dokumen JSON – satu di mana semua data dalam dokumen JSON berada pada satu baris, dan satu lagi di mana dokumen JSON berisi data pada banyak baris dengan pengembalian dan spasi disertakan
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. tambahkan({Nama_Pertama. "Rubah", Nama_Last. "Mulder", makanan_favorit. {Sarapan. "telur dan daging", Makan siang. "sandwich babi yang ditarik", Makan malam. "steak dan kentang panggang"}})
Kueri Oke, 1 item terpengaruh (0. 0007 dtk)
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. tambahkan({
-> Nama_Depan. "Rubah",
-> Nama_Belakang. "Mulder",
-> makanan_favorit. {
-> Sarapan. "Telur dan daging babi",
-> Makan siang. "sandwich babi tarik",
-> Makan malam. "steak dan kentang panggang"
-> } } )
->
Kueri Oke, 1 item terpengaruh (0. 0005 sec)
Sejauh ini, saya telah membuat skema, dan koleksi, dan saya telah menambahkan satu dokumen ke koleksi. Selanjutnya, saya akan menunjukkan cara melakukan pencarian menggunakan perintah find
Mencari DokumenUntuk menemukan semua record dalam koleksi, gunakan perintah find tanpa menentukan kriteria pencarian apa pun
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. temukan()
{
"_Indo". "00005d6fc3dc000000000027e065",
"Nama_Belakang". "Mulder",
"Nama dulu". "Rubah",
"makanan favorit". {
"Makan siang". "sandwich babi tarik",
"Makan malam". "steak dan kentang panggang",
"Sarapan". "Telur dan daging babi"
}
}
1 dokumen dalam set (0. 0002 detik)
Catatan. Kunci _id pada output di atas ditambahkan secara otomatis ke setiap dokumen dan nilai _id tidak dapat diubah. Juga, indeks dibuat secara otomatis pada kolom _id. Anda dapat memeriksa indeks menggunakan perintah SQL SHOW INDEX. (Untuk membuat output lebih mudah dilihat, saya mengubah formatnya menjadi vertikal dan saya mengubahnya kembali ke tabel)
SayaSQL 127. 0. 0. 1. 33060+ ssl workshop JS shell. pilihan. set('resultFormat','vertikal')
SayaSQL 127. 0. 0. 1. 33060+ ssl lokakarya sesi JS . runSql('TAMPILKAN INDEX DARI bengkel. makanan')
**************************** 1. baris ***************************
Meja. makanan
Tidak_unik. 0
Nama_kunci. UTAMA
Seq_in_index. 1
Nama kolom. _Indo
Pemeriksaan. SEBUAH
Kardinalitas. 1
Sub_bagian. BATAL
Penuh sesak. BATAL
Batal.
Index_type. BREE
Komentar.
Index_comment.
Bisa dilihat. YA
Ekspresi. BATAL
1 baris dalam set (0. 0003 detik)
MySQL 127. 0. 0. 1. 33060+ ssl workshop JS shell. pilihan. set('resultFormat','tabel')
Inilah cara Anda melakukan pencarian dengan menentukan kriteria pencarian. Perintah ini akan mencari nama depan Fox melalui tombol Name_First
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find('Nama_Pertama = "Rubah"')
{
"_Indo". "00005d6fc3dc000000000027e065",
"Nama_Belakang". "Mulder",
"Nama dulu". "Rubah",
"makanan favorit". {
"Makan siang". "sandwich babi tarik",
"Makan malam". "steak dan kentang panggang",
"Sarapan". "Telur dan daging babi"
}
}
1 dokumen dalam set (0. 0004 dtk)
Dan, jika hasil pencarian Anda tidak menemukan dokumen yang cocok, Anda akan mendapatkan kembalian Set kosong dan waktu yang diperlukan untuk menjalankan kueri
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find('Nama_Depan = "Jason"')
Himpunan kosong (0. 0003 detik)
Pencarian pertama mengembalikan semua kunci dalam dokumen karena saya tidak menentukan kriteria pencarian apa pun di dalam perintah find – db. makanan. Temukan(). Contoh ini adalah bagaimana Anda mengambil hanya satu kunci di dalam dokumen. Dan perhatikan bahwa key favorite_food berisi daftar sub-key di dalam sebuah key
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find('Nama_Pertama = "Rubah"'). bidang('makanan_favorite')
{
"makanan favorit". {
"Makan siang". “sandwich babi yang ditarik”,
"Makan malam". “steak dan kentang panggang”,
"Sarapan". "Telur dan daging babi"
}
}
1 dokumen dalam set (0. 0004 dtk)
Untuk mengembalikan beberapa kunci, tambahkan kunci tambahan di dalam bagian bidang, dipisahkan dengan koma
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find('Nama_Pertama = "Rubah"'). bidang("makanan_favorit", "Nama_Belakang")
{
“Nama_Belakang”. “Mulder”,
"makanan favorit". {
"Makan siang". “sandwich babi yang ditarik”,
"Makan malam". “steak dan kentang panggang”,
"Sarapan". "Telur dan daging babi"
}
}
1 dokumen dalam set (0. 0003 detik)
Catatan. Bidang dikembalikan dalam urutan penyimpanannya dalam dokumen, dan bukan dalam urutan bagian bidang. Urutan bidang kueri tidak menjamin urutan tampilan yang sama
Untuk mengembalikan subkunci dari daftar, Anda menambahkan kunci sebelum nilai subkunci di bagian bidang. Penelusuran ini akan menghasilkan nilai untuk subkunci Sarapan di kunci makanan_favorit
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find( 'Nama_Pertama = "Rubah"' ). bidang ("makanan_favorit. Sarapan" )
{
"makanan favorit. Sarapan". "Telur dan daging babi"
}
1 dokumen dalam set (0. 0002 detik)
Untuk memodifikasi dokumen, Anda menggunakan perintah modifikasi, bersama dengan kriteria pencarian untuk menemukan dokumen yang ingin Anda ubah. Ini adalah dokumen JSON asli
{
"_Indo". “00005d6fc3dc000000000027e065”,
“Nama_Belakang”. “Mulder”,
"Nama dulu". "Rubah",
"makanan favorit". {
"Makan siang". “sandwich babi yang ditarik”,
"Makan malam". “steak dan kentang panggang”,
“Breakfast”. "Telur dan daging babi"
}
}
Saya akan mengubah subkunci makanan favorit Fox menjadi Makan Siang menjadi "sup dalam mangkuk roti" dan Makan Malam menjadi "steak dan brokoli"
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. modifikasi("Nama_Pertama = 'Fox'"). set("makanan_favorit", {Makan siang. "Sup dalam mangkuk roti", Makan malam. "steak dan brokoli"})
Kueri Oke, 0 item terpengaruh (0. 0052 dtk)
Saya bisa melihat perubahannya dengan mengeluarkan perintah find, mencari Name_First sama dengan "Fox"
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find('Nama_Pertama = "Rubah"')
{
"_Indo". "00005d6fc3dc000000000027e065",
"Nama_Belakang". "Mulder",
"Nama dulu". "Rubah",
"makanan favorit". {
"Makan siang". "Sup dalam mangkuk roti",
"Makan malam". "steak dan brokoli"
}
}
1 dokumen dalam set (0. 0004 dtk)
Perhatikan bahwa subkunci Sarapan tidak lagi ada dalam daftar. Ini karena saya mengubah daftar makanan_favorit menjadi hanya makan siang dan makan malam
Untuk mengubah hanya satu subkunci di bawah makanan favorit, seperti Makan Malam, saya dapat melakukannya dengan perintah set
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. modifikasi("Nama_Pertama = 'Fox'"). set('makanan_favorit. Makan malam', 'Pizza')
Kueri Oke, 1 item terpengaruh (0. 0038 dtk)
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find("Name_First='Fox'")
{
"_Indo". "00005d81224000000000000000002",
"Nama_Belakang". "Mulder",
"Nama dulu". "Rubah",
"makanan favorit". {
"Makan siang". "Sup dalam mangkuk roti",
"Makan malam". "Pizza",
}
}
Jika saya ingin menghapus satu sub-kunci di bawah favorite_food, saya dapat menggunakan perintah unset. Saya akan menghapus subkunci Makan Malam
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. modifikasi("Nama_Pertama = 'Fox'"). tidak disetel("makanan_favorit. Makan malam")
Kueri Oke, 1 item terpengaruh (0. 0037 dtk)
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find("Name_First='Fox'")
{
"_Indo". "00005d81224000000000000000002",
"Nama_Belakang". "Mulder",
"Nama dulu". "Rubah",
"makanan favorit". {
"Makan siang". "Sup dalam mangkuk roti",
}
}
Makanan_favorit kunci berisi daftar, tetapi kunci juga dapat berisi larik. Perbedaan format utama dalam array dan daftar adalah array memiliki tanda kurung siku buka dan tutup [ ]. Tanda kurung berada di luar nilai untuk array
Berikut adalah dokumen asli dengan daftar favorite_food
db. makanan. menambahkan( {
Nama dulu. "Rubah",
Nama_Belakang. "Mulder",
makanan favorit. {
Sarapan. "Telur dan daging babi",
Makan siang. "sandwich babi tarik",
Makan malam. "steak dan kentang panggang"
}
}
)
Saya akan menghapus dokumen untuk Fox, konfirmasi penghapusan terjadi dan masukkan dokumen baru – tapi kali ini, saya akan menggunakan array untuk favorite_food (dan perhatikan tanda kurung siku)
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. hapus('Nama_Pertama = "Rubah"')
Kueri Oke, 1 item terpengaruh (0. 0093 dtk)
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find("Name_First='Fox'")
Himpunan kosong (0. 0003 detik)
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. tambahkan({
-> Nama_Depan. "Rubah",
-> Nama_Belakang. "Mulder",
-> makanan_favorit. [ {
-> Sarapan. "Telur dan daging babi",
-> Makan siang. "sandwich babi tarik",
-> Dinner. "steak dan kentang panggang"
-> } ]
-> }
-> )
->
Kueri Oke, 1 item terpengaruh (0. 0078 dtk)
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find("Name_First='Fox'")
{
"_Indo". "00005da090640000000000027e068",
"Nama_Belakang". "Mulder",
"Nama dulu". "Rubah",
"makanan favorit". [
{
"Makan siang". "sandwich babi tarik",
"Makan malam". "steak dan kentang panggang",
"Sarapan". "Telur dan daging babi"
}
]
}
1 dokumen dalam set (0. 0004 dtk)
Saat berurusan dengan array, saya dapat memodifikasi setiap elemen dalam array, atau saya dapat menambahkan elemen array lainnya. Saya akan menambahkan Snack favorit Fox ke array dengan perintah arrayAppend
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. modifikasi("Nama_Pertama = 'Fox'"). arrayAppend("makanan_favorite", {Snack. "Biji bunga matahari"})
Kueri Oke, 1 item terpengaruh (0. 0048 dtk)
Baris cocok. 1 Changed. 1 Warnings. 0
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find("Name_First='Fox'")
{
“_id”. “00005da09064000000000027e068”,
“Nama_Belakang”. “Mulder”,
"Nama dulu". "Rubah",
“favorite_food”. [
{
"Makan siang". “sandwich babi yang ditarik”,
"Makan malam". “steak dan kentang panggang”,
"Sarapan". "Telur dan daging babi"
},
{
“Snack”. “Sunflower seeds”
}
]
}
1 dokumen dalam set (0. 0004 dtk)
The key favorite_food now contains an array with two separate values in it. The sub-keys in the array position favorite_food[0] contains values for Lunch, Breakfast and Dinner values, while the array position favorite_food[1] only contains the Snack value
Note. If you aren't familiar with arrays, an array is like a list that contains elements. Elements of the array are contained in memory locations relative to the beginning of the array. The first element in the array is actually zero (0) elements away from the beginning of the array. So, the placement of the first element is denoted as array position zero (0) and this is designated by [0]. Most programming languages have been designed this way, so indexing from zero (0) is pretty much inherent to most languages
I can now delete an element in an array with the arrayDelete command. I am going to remove the Snack array member, which is favorite_food[1]
MySQL 127. 0. 0. 1. 33060+ ssl workshop JS db. foods. modify("Name_First = 'Fox'"). arrayDelete("$. favorite_food[1]")
Query OK, 1 item affected (0. 0035 sec)
Baris cocok. 1 Changed. 1 Warnings. 0
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find("Name_First='Fox'")
{
"_Indo". "00005da090640000000000027e068",
"Nama_Belakang". "Mulder",
"Nama dulu". "Rubah",
"makanan favorit". [
{
"Makan siang". "sandwich babi tarik",
"Makan malam". "steak dan kentang panggang",
"Sarapan". "Telur dan daging babi"
}
]
}
1 dokumen dalam set (0. 0004 dtk)
If I want to add an extra key, I need to add a few more variables and their values. I will need to define my schema and collection. Then I can use the patch command to add a key to an existing document
I am going to add a middle name to Fox's document
MySQL 127. 0. 0. 1. 33060+ ssl workshop JS var schema = session. getSchema('workshop')
MySQL 127. 0. 0. 1. 33060+ ssl workshop JS var collection = schema. getCollection('foods')
MySQL 127. 0. 0. 1. 33060+ ssl workshop JS collection. modify("Name_First = 'Fox'"). patch({ Name_Middle. 'William' })
Query OK, 1 item affected (0. 0020 sec)
Baris cocok. 1 Changed. 1 Warnings. 0
SayaSQL 127. 0. 0. 1. 33060+ ssl bengkel JS db. makanan. find("Name_First='Fox'")
{
"_Indo". "00005da090640000000000027e068",
"Nama_Belakang". "Mulder",
"Nama dulu". "Rubah",
"Name_Middle". "William",
"makanan favorit". [
{
"Makan siang". "sandwich babi tarik",
"Makan malam". "steak dan kentang panggang",
"Sarapan". "Telur dan daging babi"
}
]
}
1 dokumen dalam set (0. 0004 dtk)
In order to demonstrate indexes, I will need to import a large amount of data. Before I import data, I need to be sure that I have a variable set for the mysqlx client protocol to allow larger client packets. Specifically, I need to set the mysqlx_max_allowed_packet variable to the largest allowable size of 1TB. You can set this variable in the MySQL configuration file (my. cnf or my. ini) and reboot the MySQL instance, or you can set it for your session
I can check the values of the mysqlx_max_allowed_packet variable from within Shell, and if it isn't set to 1TB, I will modify it for this session. (I can see the value for mysqlx_max_allowed_packet is set to 100MB or 100 megabytes)
MySQL 127. 0. 0. 1. 33060+ ssl JS session. runSql('SHOW VARIABLES like "%packet%"')
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
Variable_name . Value
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
max_allowed_packet . 943718400
mysqlx_max_allowed_packet . 104857600
slave_max_allowed_packet . 1073741824
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
3 rows in set (0. 0021 sec)
MySQL 127. 0. 0. 1. 33060+ ssl JS session. runSql('SET @@GLOBAL. mysqlx_max_allowed_packet = 1073741824')
Query OK, 0 rows affected (0. 0004 sec)
MySQL 127. 0. 0. 1. 33060+ ssl JS session. runSql('SHOW VARIABLES like "%packet%"')
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
Variable_name . Value
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
max_allowed_packet . 943718400
mysqlx_max_allowed_packet . 1073741824
slave_max_allowed_packet . 1073741824
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
3 rows in set (0. 0021 sec)
I don't want to write this large import to the binary log (I have binlog enabled), so I can use the SQL command to first. I am going to create a new collection named project, and then import a 400+ megabyte JSON file into the new collection
MySQL 127. 0. 0. 1. 33060+ ssl JS session. runSql('SET SQL_LOG_BIN=0')
Query OK, 0 rows affected (0. 0464 sec)
MySQL 127. 0. 0. 1. 33060+ ssl JS session. createSchema("project")
MySQL 127. 0. 0. 1. 33060+ ssl JS \use project
Default schema `project` accessible through db
MySQL 127. 0. 0. 1. 33060+ ssl JS util. importJson(". /workshop/Doc_Store_Demo_File. json", {schema. "project", collection. "GoverningPersons"})
Importing from file ". /workshop/Doc_Store_Demo_File. json" to collection `project`. `GoverningPersons` in MySQL Server at 127. 0. 0. 1. 33060
2613346. 2613346
Processed 415. 71 MB in 2613346 documents in 2 min 51. 0696 sec (15. 28K documents/s)
Total successfully imported documents 2613346 (15. 28K documents/s)
As you can see from the output above, I imported 2,613,346 documents
Note. This imported JSON document contains public data regarding businesses in the State of Washington
Now that I have my 2. 6 million documents in the database, I will do a quick search and limit the results to one record by using the limit command. Ini akan menampilkan kunci dalam dokumen
MySQL 127. 0. 0. 1. 33060+ ssl JS \use project
Default schema `project` accessible through db
MySQL 127. 0. 0. 1. 33060+ ssl JS db. GoverningPersons. find(). limit(1)
{
"Ubi". "601544680",
"Zip". "99205",
"_id". "00005d6fc3dc000000000027e067",
"City". "SPOKANE",
"State". "WA",
"Judul". "GUBERNUR",
"Address". "RT 5",
"Nama keluarga". "FRISCH",
"FirstName". "BOB",
"Nama tengah". ""
}
1 dokumen dalam set (0. 0022 sec)
Again – notice an index for the key _id has automatically been added
MySQL 127. 0. 0. 1. 33060+ ssl JS session. runSql('SHOW INDEX FROM project. GoverningPersons')
************************** 1. baris ***************************
Table. GoverningPersons
Non_unique. 0
Key_name. PRIMARY
Seq_in_index. 1
Column_name. _id
Collation. A
Cardinality. 2481169
Sub_part. NULL
Packed. NULL
Null
Index_type. BTREE
Comment
Index_comment
Visible. YES
Expression. NULL
1 row in set (0. 0137 sec)
To demonstrate indexes on a large dataset, I will perform two searches against all documents in the project collection – where LastName is equal to FRISCH and where LastName is equal to VARNELL. Then, I will create the index on LastName, and re-run the two find queries. Note. I am not displaying all of the returned documents to save space
MySQL 127. 0. 0. 1. 33060+ ssl JS db. Pemerintahan. find('LastName = "FRISCH"')
{
"Ubi". "601544680",
"Zip". "99205",
"_id". "00005da09064000000000027e069",
"City". "SPOKANE",
"State". "WA",
"Judul". "GUBERNUR",
"Address". "RT 5",
"Nama keluarga". "FRISCH",
"FirstName". "BOB",
"Nama tengah". ""
}
.
82 documents in set (1. 3559 sec)
MySQL 127. 0. 0. 1. 33060+ ssl JS db. GoverningPersons. find('LastName = "VARNELL"')
{
"Ubi". "602268651",
"Zip". "98166",
"_id". "00005da09064000000000028ffdc",
"City". "SEATTLE",
"State". "WA",
"Judul". "GUBERNUR",
"Address". "18150 MARINE VIEW DR SW",
"LastName". "VARNELL",
"FirstName". "JAMES",
"Nama tengah". ""
}
.
33 documents in set (1. 0854 sec)
The searches took 1. 3559 and 1. 0854 seconds, respectively. I can now create an index on LastName. When I create an index, I have to specify the data type for that particular key. And for a text key, I have to specify how many characters of that key I want to include in the index. (Note. see TEXT(20) in the command below)
MySQL 127. 0. 0. 1. 33060+ ssl JS db. GoverningPersons. createIndex("i_Name_Last", {fields. [{field. "$. LastName", type. "TEXT(20)"}]})
Query OK, 0 rows affected (8. 0653 sec)
Now I will re-run the same two queries where LastName equals FRISCH and where LastName equals VARNELL
MySQL 127. 0. 0. 1. 33060+ ssl JS db. Pemerintahan. find('LastName = "FRISCH"')
{
"Ubi". "601544680",
"Zip". "99205",
"_id". "00005da09064000000000027e069",
"City". "SPOKANE",
"State". "WA",
"Judul". "GUBERNUR",
"Address". "RT 5",
"Nama keluarga". "FRISCH",
"FirstName". "BOB",
"Nama tengah". ""
}
.
82 documents in set (0. 0097 sec)
MySQL 127. 0. 0. 1. 33060+ ssl JS db. GoverningPersons. find('LastName = "VARNELL"')
{
"Ubi". "602268651",
"Zip". "98166",
"_id". "00005da09064000000000028ffdc",
"City". "SEATTLE",
"State". "WA",
"Judul". "GUBERNUR",
"Address". "18150 MARINE VIEW DR SW",
"LastName". "VARNELL",
"FirstName". "JAMES",
"Nama tengah". ""
}
.
33 documents in set (0. 0008 sec)
The queries ran much faster with an index – 0. 0097 seconds and 0. 0008 seconds. Not bad for searching 2. 6 million records
Note. The computer I was using for this post is a Hackintosh, running Mac OS 10. 13. 6, with an Intel i7-8700K 3. 7GHz processor with six cores, with 32GB 2666MHz DDR4 RAM, and an SATA III 6 Gb/s, M. 2 2280 SSD. Your performance results may vary
And I can take a look at the index for LastName
MySQL 127. 0. 0. 1. 33060+ ssl JS shell. options. set('resultFormat','vertical')
MySQL 127. 0. 0. 1. 33060+ ssl JS session. runSql('SHOW INDEX FROM project. GoverningPersons')
************************** 1. baris ***************************
Table. GoverningPersons
Non_unique. 0
Key_name. PRIMARY
Seq_in_index. 1
Column_name. _id
Collation. A
Cardinality. 2481169
Sub_part. NULL
Packed. NULL
Null
Index_type. BTREE
Comment
Index_comment
Visible. YES
Expression. NULL
************************** 2. baris ***************************
Table. GoverningPersons
Non_unique. 1
Key_name. i_Name_Last
Seq_in_index. 1
Column_name. $ix_t20_F1A785D3F25567CD94716D955607AADB04BB3C0E
Collation. A
Cardinality. 300159
Sub_part. 20
Packed. NULL
Null. YES
Index_type. BTREE
Comment
Index_comment
Visible. YES
Expression. NULL
2 rows in set (0. 0059 sec)
SayaSQL 127. 0. 0. 1. 33060+ ssl JS cangkang. pilihan. set('resultFormat','tabel')
I can create an index on multiple columns as well. Here is an index created on the State and Zip fields
MySQL 127. 0. 0. 1. 33060+ ssl JS db. GoverningPersons. createIndex('i_state_zip', {fields. [ {field. '$. State', type. 'TEXT(2)'}, {field. '$. Zip', type. 'TEXT(10)'}]})
Query OK, 0 rows affected (10. 4536 sec)
I can take a look at the index as well (the other index results were removed to save space)
MySQL 127. 0. 0. 1. 33060+ ssl JS shell. options. set('resultFormat','vertical')
MySQL 127. 0. 0. 1. 33060+ ssl JS session. runSql('SHOW INDEX FROM project. GoverningPersons')
.
************************** 3. row ***************************
Table. GoverningPersons
Non_unique. 1
Key_name. i_state_zip
Seq_in_index. 1
Nama kolom. $ix_t2_00FFBF570DC47A52910DDA38C0C1FB1361F0426A
Collation. A
Kardinalitas. 864
Sub_bagian. 2
Packed. NULL
Null. YES
Index_type. BTREE
Comment
Index_comment
Visible. YES
Expression. NULL
************************** 4. baris ***************************
Table. GoverningPersons
Non_unique. 1
Key_name. i_state_zip
Seq_in_index. 2
Column_name. $ix_t10_18619E3AC96C74FECCF6B622D9DB0864C2938AB6
Collation. A
Cardinality. 215626
Sub_part. 10
Packed. NULL
Null. YES
Index_type. BTREE
Comment
Index_comment
Visible. YES
Expression. NULL
4 rows in set (0. 0066 sec)
SayaSQL 127. 0. 0. 1. 33060+ ssl JS cangkang. pilihan. set('resultFormat','tabel')
I will run a query looking for the first entry FRISH based upon his state (WA) and Zip (99205). The query result time is still pretty good, even though I am also returning his LastName
MySQL 127. 0. 0. 1. 33060+ ssl JS db. GoverningPersons. find("State='WA' AND Zip = '99205' AND LastName = 'FRISCH'")
{
"Ubi". "601544680",
"Zip". "99205",
"_id". "00005da09064000000000027e069",
"City". "SPOKANE",
"State". "WA",
"Judul". "GUBERNUR",
"Address". "RT 5",
"Nama keluarga". "FRISCH",
"FirstName". "BOB",
"Nama tengah". ""
}
1 document in set (0. 0011 sec)
To drop an index, use the dropIndex command
MySQL 127. 0. 0. 1. 33060+ ssl JS db. GoverningPersons. dropIndex("i_Name_Last")
NoSQL and SQL in the same databaseThe great advantage to using MySQL to store JSON documents is that the data is stored in the InnoDB storage engine. So, the NoSQL document store database has features and benefits of InnoDB – such as transactions and ACID-compliance. This also means that you can use MySQL features like replication, group replication, transparent data encryption, etc. And if you need to restore a backup and play back the binlog files for a point-in-time recovery, you can do that as well, as all of the NoSQL transactions are written to the MySQL Binary Log. Here is what a NoSQL transaction looks like in the MySQL binary log
NoSQL
koleksi. modify(""Name_First = 'Selena'"). patch({ Name_Middle. 'Kitty' })
MySQL Binlog
# at 3102
#191018 11. 17. 41 server id 3451 end_log_pos 3384 CRC32 0xd0c12cca Query thread_id=15 exec_time=0 error_code=0
SET TIMESTAMP=1571411861/*. */;
UPDATE `workshop`. `foods` SET doc=JSON_SET(JSON_MERGE_PATCH(doc, JSON_OBJECT('Name_Middle','Kitty')),'$. _id',JSON_EXTRACT(`doc`,'$. _id')) WHERE (JSON_EXTRACT(doc,'$. Name_First') = 'Selena')
/*. */;
# at 3384
#191018 11. 17. 41 server id 3451 end_log_pos 3415 CRC32 0xe0eaf4ef Xid = 246
COMMIT/*. */;
SET @@SESSION. GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*. */;
DELIMITER ;
Finally, here is an example of how to do a transaction
MySQL 127. 0. 0. 1. 33060+ ssl JS session. startTransaction()
Query OK, 0 rows affected (0. 0013 sec)
MySQL 127. 0. 0. 1. 33060+ ssl JS db. GoverningPersons. modify("Ubi = '601544680'"). set("MiddleName", "PETER")
Query OK, 1 item affected (0. 0089 sec)
Baris cocok. 1 Changed. 1 Warnings. 0
MySQL 127. 0. 0. 1. 33060+ ssl JS session. rollback()
Query OK, 0 rows affected (0. 0007 sec)
MySQL 127. 0. 0. 1. 33060+ ssl JS db. GoverningPersons. modify("Ubi = '601544680'"). set("MiddleName", "STEVEN")
Query OK, 1 item affected (0. 0021 sec)
Baris cocok. 1 Changed. 1 Warnings. 0
MySQL 127. 0. 0. 1. 33060+ ssl JS session. commit()
Query OK, 0 rows affected (0. 0002 sec)
Since the MySQL Document Store utilizes the MySQL database server and since the documents are stored in InnoDB, you can also use MySQL SQL JSON functions as well to manipulate the data stored in either a JSON document or in a JSON data type. Here is a list of the JSON functions available, and while JSON functions were introduced in 5. 7, not all of these functions will be in 5. 7 – but they are all in version 8. 0
JSON_ARRAYJSON_ARRAY_APPENDJSON_ARRAY_INSERTJSON_CONTAINSJSON_CONTAINS_PATHJSON_DEPTHJSON_EXTRACTJSON_INSERTJSON_KEYSJSON_LENGTHJSON_MERGE_PATCHJSON_MERGE_PRESERVEJSON_OBJECTJSON_OVERLAPSJSON_PRETTYJSON_QUOTEJSON_REMOVEJSON_REPLACEJSON_SCHEMA_VALIDJSON_SCHEMA_VALIDATION_REPORTJSON_SEARCHJSON_SETJSON_STORAGE_FREEJSON_STORAGE_SIZEJSON_TABLEJSON_TYPEJSON_UNQUOTEJSON_VALIDMEMBER OFBut you already use Mongo? And you have MySQL as well?If you already use Mongo and MySQL, and you want to switch to MySQL, or if your DBA's already know Mongo, then moving to MySQL or using the MySQL doc store is pretty easy. The commands used in Mongo are very similar to the ones used in the MySQL Document Store. The login command is similar to the MySQL “regular” client command in that you specify the user and password with the -u and -p
In MySQL Shell, you put the username followed by the @ and the IP address. And you can specify which database or schema you want to use upon login. Mongo memang memiliki beberapa pintasan dengan perintah show, seperti pada show dbs, show schemas, atau show collections. But you can do almost the same in MySQL Shell by setting variables to equal certain functions or commands (like I did earlier. )
To create a schemda/database in Mongo, you simply execute the use database name command – and it creates the schema/database if it doesn't exist. The other commands having to do with collections are almost the same
Where there are differences, they are relatively small. Mongo uses the command insert and MySQL uses add when inserting documents. But then, other commands such as the remove document command are the same
As for the last item in the table below, native Mongo can't run any SQL commands (without using a third-party software GUI tool) – so again, if you have MySQL DBA's on staff, the learning curve can be lower because they can use SQL commands if they don't remember the NoSQL commands
CommandMongoMySQL Document Store (via Shell)Loginmongo ‐u ‐p ‐‐databasemysqlsh root@127. 0. 0. 1 ‐‐databaseSelect schemause database_name\use database_nameShow schemas/dbsshow dbssession. getSchemas()Create schemause database_namesession. createSchema()Show collectionsshow collections or db. getCollectionNames();db. getCollections()Create collectiondb. createCollection("collectionName");db. createCollection("collectionName");Insert documentdb. insert({field1. "value", field2. "value"})db. insert({field1. "value", field2. "value"})Remove documentdb. remove()db. remove()Run SQL commandsession. runSql(SQL_command)So, with MySQL, you have a hybrid solution where you can use both SQL and NoSQL at the same time. And, since you are storing the JSON documents inside MySQL, you an also use the MySQL JSON functions to search, update and delete the same records