<p>To create a new transaction (with createrawtransaction) you will require a previous unspent vout - i.e. a UTXO, which is the result of the listunspent command. This will be the input of the transaction. The output of the transaction will be created on the fly, based on the amount and destination address. A transaction fee is likely necessary, and is calculated as <code>tx fee = (sum of inputs) - (sum of outputs)</code>. Note that miners discriminate transactions based on the fee/vbyte, so a larger transaction (byte-size, not btc spent) needs a higher fee to be competitive. </p>

<p>The only thing you will need to store in your database is the vouts, indexed with the address or script hash. On each new block you will have to update those vouts, removing the ones that have been spent and add new unspent ones. </p>

CC BY-SA 4.0
2018-07-30T11:57:00.320Z
Can you indicate which part should I parser and store it in my sample output ?
Frank AK
CC BY-SA 4.0
2018-07-30T13:04:56.690Z
The things you need to store for a vout is the txid and the output number(n). Also you need to store the address/scripthash so that when a client queries for specific keys he gets the required results.
Nikos Kostoulas
CC BY-SA 4.0
2018-07-30T13:05:20.090Z
Then when you create a new transaction you only need to provide 'txid' and 'n'.

The client though also needs to sign this 'vin' with the private key corresponding to this script/pubkey address.
Nikos Kostoulas
CC BY-SA 4.0
2018-07-30T13:06:59.460Z
Could you provide me some example, I really newbie at the bitcoin :)
Frank AK
CC BY-SA 4.0
2018-07-30T13:26:15.883Z
http://chainquery.com/bitcoin-api/createrawtransaction
\n


https://en.bitcoin.it/wiki/Transaction
Nikos Kostoulas
CC BY-SA 4.0
2018-07-30T17:57:49.723Z
Edit: transaction fees are not included as a vout, they are simply included as an excess of input value compared to outputs.
chytrik
CC BY-SA 4.0
2018-07-31T02:10:57.713Z
@NikosKostoulas Sure, I can store every transaction into the mysql. But as the HD wallets exist, I can't figure which address belong to the same account. Let's say, If Alice have 20 BTCs, and she send 8 BTCs to Frank, and get 12 BTCs back to a change address, I don't know how to group those unspent . Anyone suggestions in your answer?
Frank AK
CC BY-SA 4.0
2018-08-01T07:27:52.890Z
The unspent contains a scriptPubKey where btc was paid to. It also contains an address. It's these address you need to use to store the unspent with. Alice should be able to look up her own unspent based on the addresses stored on her wallet.
Nikos Kostoulas
CC BY-SA 4.0
2018-07-30T11:10:08.553Z
2018-07-30T17:56:37.420Z
2018-07-30T17:56:37.420Z
Nikos Kostoulas
<p>AFAIK, To send a raw transaction, which include both <code>input</code> and <code>output</code>. And What if we run it on the local node, we can simply fetch the unspent list by run <code>bitcoin-cli listunspent</code>, but If I just want to create a raw transaction on the client side, I believe the only way it store all the transaction to my local server. and before store it, I should do the following operations:</p>

<ul>
<li>1.get raw transaction (getrawtransaction)</li>
<li>2.parser raw transaction (decoderawtransaction)</li>
<li>3.get the value of amount, and transaction id</li>
<li>4.put all together insert it into the mysql database</li>
</ul>

<p>The table struct as :</p>

<pre><code>mysql&gt; desc t_tx;
+---------------+---------------+------+-----+---------------------+-------------------------------+
| Field         | Type          | Null | Key | Default             | Extra                         |
+---------------+---------------+------+-----+---------------------+-------------------------------+
| txid          | char(64)      | YES  |     | NULL                |                               |
| vout          | int(11)       | YES  |     | NULL                |                               |
| address       | char(35)      | NO   | PRI | NULL                |                               |
| redeemScript  | char(44)      | YES  |     | NULL                |                               |
| scriptPubKey  | char(46)      | YES  |     | NULL                |                               |
| amount        | decimal(16,8) | YES  |     | NULL                |                               |
| confirmations | bigint(20)    | YES  |     | NULL                |                               |
| spendable     | char(8)       | YES  |     | NULL                |                               |
| solvable      | char(8)       | YES  |     | NULL                |                               |
| safe          | char(8)       | YES  |     | NULL                |                               |
| blockhash     | char(64)      | YES  |     | NULL                |                               |
| created       | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+---------------+---------------+------+-----+---------------------+-------------------------------+
</code></pre>

<p>The table look so redundancy, I guess there are many columns I can remove from my table.  </p>

<p>Look the output as below, If your guys can get a comment to each field at the end will be a really good for everyone, as I do at the end of <code>txid</code>.</p>

<pre><code>[frank@localhost bin]$ ./bitcoin-cli decoderawtransaction 0200000001dbceae939bd37f2fe9dff0fca2aeaf44ec4987a34d59d58012982f49248d78fa0000000049483045022100f49a4e30e3baa5f00119f20e12a9bc932eea0814f7841ce0dc0ac17808d17e6e02207a50138fd23e7ac951fec96ff9869ecfbd495504889163a6d351ca07d2385de801feffffff02d8b068590000000017a914f393140f96b131c20ab063f025694dcade19e21e8700ca9a3b0000000017a914fc5d7d9af26be5f5656ac88023d1ff1683212dcc8737010000
{
  "txid": "172b99d21c02430fa16a70883d10090fde39c087ca82ebdf5a1b2567b115272e", // transaction id
  "hash": "172b99d21c02430fa16a70883d10090fde39c087ca82ebdf5a1b2567b115272e",
  "version": 2,
  "size": 188,
  "vsize": 188,
  "locktime": 311,
  "vin": [
    {
      "txid": "fa788d24492f981280d5594da38749ec44afaea2fcf0dfe92f7fd39b93aecedb",
      "vout": 0,
      "scriptSig": {
        "asm": "3045022100f49a4e30e3baa5f00119f20e12a9bc932eea0814f7841ce0dc0ac17808d17e6e02207a50138fd23e7ac951fec96ff9869ecfbd495504889163a6d351ca07d2385de8[ALL]",
        "hex": "483045022100f49a4e30e3baa5f00119f20e12a9bc932eea0814f7841ce0dc0ac17808d17e6e02207a50138fd23e7ac951fec96ff9869ecfbd495504889163a6d351ca07d2385de801"
      },
      "sequence": 4294967294
    }
  ],
  "vout": [
    {
      "value": 15.00033240,
      "n": 0,
      "scriptPubKey": {
        "asm": "OP_HASH160 f393140f96b131c20ab063f025694dcade19e21e OP_EQUAL",
        "hex": "a914f393140f96b131c20ab063f025694dcade19e21e87",
        "reqSigs": 1,
        "type": "scripthash",
        "addresses": [
          "2NFT8P5jxEGhSMY1xGqpADyPztK3pmh5pWb"
        ]
      }
    },
    {
      "value": 10.00000000,
      "n": 1,
      "scriptPubKey": {
        "asm": "OP_HASH160 fc5d7d9af26be5f5656ac88023d1ff1683212dcc OP_EQUAL",
        "hex": "a914fc5d7d9af26be5f5656ac88023d1ff1683212dcc87",
        "reqSigs": 1,
        "type": "scripthash",
        "addresses": [
          "2NGFcGUAUd9KyMpCJhqVZPfJ2mTi1rLvTtU"
        ]
      }
    }
  ]
}
</code></pre>

<p>As we know, the <code>createrawtransaction</code> function require an input and an output. but both input and output are array type, which means you can put more than one unspend transaction as input, and more than one dest address with amount into the output. but if I just want to create the raw transaction and sign it at the client side (mobile app), I need the node server support with api, which can call with an address and response all the unspent back!</p>

<p>I have tried all the command from <code>bitcoin-cli</code>, but non of them are useful at the scenes. so I want to subscribe the node information by <code>zeroMQ</code>, and then parser the <code>txid</code> by (getrawtransaction &amp; decoderawtransaction), and then store all the related information into the mysql. But I don't know how to make this happend, because I have a little be confuse with the output from <code>decoderawtransaction</code>, and one can explain each field means? Especially, the <code>vout</code> inside the <code>vin</code>. </p>

<p>BTW, if you in think my solution is bad, please correct me, I am really newbie at the bitcoin!</p>

<raw-transaction><listunspent><mysql>
What's the best way to store unspent transaction?
Frank AK
# What's the best way to store unspent transaction?

(Asked by: username_0 on 2018-07-30)

AFAIK, To send a raw transaction, which include both `input` and `output`. And What if we run it on the local node, we can simply fetch the unspent list by run `bitcoin-cli listunspent`, but If I just want to create a raw transaction on the client side, I believe the only way it store all the transaction to my local server. and before store it, I should do the following operations:

* 1.get raw transaction (getrawtransaction)
* 2.parser raw transaction (decoderawtransaction)
* 3.get the value of amount, and transaction id
* 4.put all together insert it into the mysql database

The table struct as :

```
mysql> desc t_tx;
+---------------+---------------+------+-----+---------------------+-------------------------------+
| Field         | Type          | Null | Key | Default             | Extra                         |
+---------------+---------------+------+-----+---------------------+-------------------------------+
| txid          | char(64)      | YES  |     | NULL                |                               |
| vout          | int(11)       | YES  |     | NULL                |                               |
| address       | char(35)      | NO   | PRI | NULL                |                               |
| redeemScript  | char(44)      | YES  |     | NULL                |                               |
| scriptPubKey  | char(46)      | YES  |     | NULL                |                               |
| amount        | decimal(16,8) | YES  |     | NULL                |                               |
| confirmations | bigint(20)    | YES  |     | NULL                |                               |
| spendable     | char(8)       | YES  |     | NULL                |                               |
| solvable      | char(8)       | YES  |     | NULL                |                               |
| safe          | char(8)       | YES  |     | NULL                |                               |
| blockhash     | char(64)      | YES  |     | NULL                |                               |
| created       | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+---------------+---------------+------+-----+---------------------+-------------------------------+

```

The table look so redundancy, I guess there are many columns I can remove from my table.

Look the output as below, If your guys can get a comment to each field at the end will be a really good for everyone, as I do at the end of `txid`.

```
[username_0@localhost bin]$ ./bitcoin-cli decoderawtransaction 0200000001dbceae939bd37f2fe9dff0fca2aeaf44ec4987a34d59d58012982f49248d78fa0000000049483045022100f49a4e30e3baa5f00119f20e12a9bc932eea0814f7841ce0dc0ac17808d17e6e02207a50138fd23e7ac951fec96ff9869ecfbd495504889163a6d351ca07d2385de801feffffff02d8b068590000000017a914f393140f96b131c20ab063f025694dcade19e21e8700ca9a3b0000000017a914fc5d7d9af26be5f5656ac88023d1ff1683212dcc8737010000
{
  "txid": "172b99d21c02430fa16a70883d10090fde39c087ca82ebdf5a1b2567b115272e", // transaction id
  "hash": "172b99d21c02430fa16a70883d10090fde39c087ca82ebdf5a1b2567b115272e",
  "version": 2,
  "size": 188,
  "vsize": 188,
  "locktime": 311,
  "vin": [
    {
      "txid": "fa788d24492f981280d5594da38749ec44afaea2fcf0dfe92f7fd39b93aecedb",
      "vout": 0,
      "scriptSig": {
        "asm": "3045022100f49a4e30e3baa5f00119f20e12a9bc932eea0814f7841ce0dc0ac17808d17e6e02207a50138fd23e7ac951fec96ff9869ecfbd495504889163a6d351ca07d2385de8[ALL]",
        "hex": "483045022100f49a4e30e3baa5f00119f20e12a9bc932eea0814f7841ce0dc0ac17808d17e6e02207a50138fd23e7ac951fec96ff9869ecfbd495504889163a6d351ca07d2385de801"
      },
      "sequence": 4294967294
    }
  ],
  "vout": [
    {
      "value": 15.00033240,
      "n": 0,
      "scriptPubKey": {
        "asm": "OP_HASH160 f393140f96b131c20ab063f025694dcade19e21e OP_EQUAL",
        "hex": "a914f393140f96b131c20ab063f025694dcade19e21e87",
        "reqSigs": 1,
        "type": "scripthash",
        "addresses": [
          "2NFT8P5jxEGhSMY1xGqpADyPztK3pmh5pWb"
        ]
      }
    },
    {
      "value": 10.00000000,
      "n": 1,
      "scriptPubKey": {
        "asm": "OP_HASH160 fc5d7d9af26be5f5656ac88023d1ff1683212dcc OP_EQUAL",
        "hex": "a914fc5d7d9af26be5f5656ac88023d1ff1683212dcc87",
        "reqSigs": 1,
        "type": "scripthash",
        "addresses": [
          "2NGFcGUAUd9KyMpCJhqVZPfJ2mTi1rLvTtU"
        ]
      }
    }
  ]
}

```

As we know, the `createrawtransaction` function require an input and an output. but both input and output are array type, which means you can put more than one unspend transaction as input, and more than one dest address with amount into the output. but if I just want to create the raw transaction and sign it at the client side (mobile app), I need the node server support with api, which can call with an address and response all the unspent back!

I have tried all the command from `bitcoin-cli`, but non of them are useful at the scenes. so I want to subscribe the node information by `zeroMQ`, and then parser the `txid` by (getrawtransaction & decoderawtransaction), and then store all the related information into the mysql. But I don't know how to make this happend, because I have a little be confuse with the output from `decoderawtransaction`, and one can explain each field means? Especially, the `vout` inside the `vin`.

BTW, if you in think my solution is bad, please correct me, I am really newbie at the bitcoin!

**Tags:** raw-transaction, listunspent, mysql


---

## 1 answers

### Answer by username_1 on 2018-07-30. Score: 1

To create a new transaction (with createrawtransaction) you will require a previous unspent vout - i.e. a UTXO, which is the result of the listunspent command. This will be the input of the transaction. The output of the transaction will be created on the fly, based on the amount and destination address. A transaction fee is likely necessary, and is calculated as `tx fee = (sum of inputs) - (sum of outputs)`. Note that miners discriminate transactions based on the fee/vbyte, so a larger transaction (byte-size, not btc spent) needs a higher fee to be competitive.

The only thing you will need to store in your database is the vouts, indexed with the address or script hash. On each new block you will have to update those vouts, removing the ones that have been spent and add new unspent ones.

> **Comments:**
> **username_0**: Can you indicate which part should I parser and store it in my sample output ?  
> **username_1**: The things you need to store for a vout is the txid and the output number(n). Also you need to store the address/scripthash so that when a client queries for specific keys he gets the required results.  
> **username_1**: Then when you create a new transaction you only need to provide 'txid' and 'n'.  The client though also needs to sign this 'vin' with the private key corresponding to this script/pubkey address.  
> **username_0**: Could you provide me some example, I really newbie at the bitcoin :)  
> **username_1**: http://chainquery.com/bitcoin-api/createrawtransaction \n   https://en.bitcoin.it/wiki/Transaction  
> **username_2**: Edit: transaction fees are not included as a vout, they are simply included as an excess of input value compared to outputs.  
> **username_0**: @username_1 Sure, I can store every transaction into the mysql. But as the HD wallets exist, I can't figure which address belong to the same account. Let's say, If Alice have 20 BTCs, and she send 8 BTCs to username_0, and get 12 BTCs back to a change address, I don't know how to group those unspent . Anyone suggestions in your answer?  
> **username_1**: The unspent contains a scriptPubKey where btc was paid to. It also contains an address. It's these address you need to use to store the unspent with. Alice should be able to look up her own unspent based on the addresses stored on her wallet.  

---

